"janne" wrote:

> Hello,
>
> I'm trying to do something like this:
>
> CREATE PROCEDURE my_proc(
>    @id uniqueidentifier, @string varchar(100))
> AS
> EXEC('SELECT something FROM somewhere WHERE table.id =' +@id)
>
> I need to have it in a string because I'm using variables for
> "something" and "somewhere". It doesn't seem to like that I'm trying to
> put the uniqueidentifier in a string...
>
> How can I get around this?
>
>

> The problem is that the uniqueidentifier datatype has a higher precedence
> than the char types. When comparing or operating with two different
> datatypes, the value with the lower precedence will be converted to the
> datatype of the value with the higher precedence.
>
> See
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp
>
> The solution here is to CAST the @id variable into a CHAR type, then build
> your query string. That way you avoid the implicit conversion.
>
> "janne" wrote:
>
> > Hello,
> >
> > I'm trying to do something like this:
> >
> > CREATE PROCEDURE my_proc(
> >    @id uniqueidentifier, @string varchar(100))
> > AS
> > EXEC('SELECT something FROM somewhere WHERE table.id =' +@id)
> >
> > I need to have it in a string because I'm using variables for
> > "something" and "somewhere". It doesn't seem to like that I'm trying to
> > put the uniqueidentifier in a string...
> >
> > How can I get around this?
> >
> >

"janne" wrote:

> But the table.id is a uniqueidentifier, how can the WHERE clause work
> if I convert the @id to CHAR?
>
> Mark Williams wrote:
> > The problem is that the uniqueidentifier datatype has a higher precedence
> > than the char types. When comparing or operating with two different
> > datatypes, the value with the lower precedence will be converted to the
> > datatype of the value with the higher precedence.
> >
> > See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp
> >
> > The solution here is to CAST the @id variable into a CHAR type, then build
> > your query string. That way you avoid the implicit conversion.
> >
> > "janne" wrote:
> >
> > > Hello,
> > >
> > > I'm trying to do something like this:
> > >
> > > CREATE PROCEDURE my_proc(
> > >    @id uniqueidentifier, @string varchar(100))
> > > AS
> > > EXEC('SELECT something FROM somewhere WHERE table.id =' +@id)
> > >
> > > I need to have it in a string because I'm using variables for
> > > "something" and "somewhere". It doesn't seem to like that I'm trying to
> > > put the uniqueidentifier in a string...
> > >
> > > How can I get around this?
> > >
> > >
>
>

"Mark Williams" wrote:

> There are three ways to specify the value of something that has the
> uniqueidentifier datatype: using NEWID(), in string format, or in hex format.
>
> DECLARE @id1 uniqueidentifier
> DECLARE @id2 uniqueidentifier
> SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
> SET @id2=0x2B8BC811D69F5E48B60A3E0F6839FFCf
> --Believe it or not, @id1=@id2
>
> SELECT 1 WHERE @id1=@id2
>
> The only operators that are allowed between things of datatype
> uniqueidentifier are comparison (<,>,=,>=,<=,<>). If you tried
>
>
> DECLARE @id1 uniqueidentifier
> DECLARE @id2 uniqueidentifier
> DECLARE @colname nvarchar(15)
> DECLARE @tblname nvarchar(15)
> DECLARE @querystring nvarchar(512)
> SET @colname=N'col1'
> SET @tblname=N'table1'
> SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
> SET @id2=0x2B8BC811D69F5E48B60A3E0F6839FFCf
>
> SET @querystring=N'SELECT ' + @colname + N' FROM ' + @tblname + N' WHERE
> id=' + @id1
>
> SELECT @querystring
>
> You get the error message:
>
> Server: Msg 403, Level 16, State 1, Line 13
> Invalid operator for data type. Operator equals add, type equals
> uniqueidentifier.
>
> So, you have to CAST the value for @id1 into a CHAR type to you can build
> your query string:
>
> DECLARE @id1 uniqueidentifier
> DECLARE @colname nvarchar(15)
> DECLARE @tblname nvarchar(15)
> DECLARE @querystring nvarchar(512)
> SET @colname=N'col1'
> SET @tblname=N'table1'
> SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
>
> SET @querystring=N'SELECT ' + @colname + N' FROM ' + @tblname + N' WHERE
> id=' +  CAST(@id1 AS nvarchar(512))
>
> This works, but look closely at the end of the WHERE condition in
> @querystring:
>
> WHERE id=11C88B2B-9FD6-485E-B60A-3E0F6839FFCF
>
> You can't specify a query string this way; it must be quoted, like a string.
> So, you have to add in the quote marks manually when building the query
> string:
>
> DECLARE @id1 uniqueidentifier
> DECLARE @colname nvarchar(15)
> DECLARE @tblname nvarchar(15)
> DECLARE @querystring nvarchar(512)
> SET @colname=N'col1'
> SET @tblname=N'table1'
> SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
>
> SET @querystring=N'SELECT ' + @colname + N' FROM ' + @tblname + N' WHERE
> id=''' +  CAST(@id1 AS nvarchar(512)) + N''''  --these are 4 single quotes
>
> SELECT @querystring
>
> Now the string is in the correct format for the query to execute.
>
> "janne" wrote:
>
> > But the table.id is a uniqueidentifier, how can the WHERE clause work
> > if I convert the @id to CHAR?
> >
> > Mark Williams wrote:
> > > The problem is that the uniqueidentifier datatype has a higher precedence
> > > than the char types. When comparing or operating with two different
> > > datatypes, the value with the lower precedence will be converted to the
> > > datatype of the value with the higher precedence.
> > >
> > > See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp
> > >
> > > The solution here is to CAST the @id variable into a CHAR type, then build
> > > your query string. That way you avoid the implicit conversion.
> > >
> > > "janne" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to do something like this:
> > > >
> > > > CREATE PROCEDURE my_proc(
> > > >    @id uniqueidentifier, @string varchar(100))
> > > > AS
> > > > EXEC('SELECT something FROM somewhere WHERE table.id =' +@id)
> > > >
> > > > I need to have it in a string because I'm using variables for
> > > > "something" and "somewhere". It doesn't seem to like that I'm trying to
> > > > put the uniqueidentifier in a string...
> > > >
> > > > How can I get around this?
> > > >
> > > >
> >
> >

> Correcting a small, but confusing, syntax mistake. Where I wrote
>
> "You can't specify a query string this way; it must be quoted, like a string.
> So, you have to add in the quote marks manually when building the query
> string:"
>
> What I really meant to say was
>
> You can't specify a uniqueidentifier value this way; it must be quoted, like
> a string.
> So, you have to add in the quote marks manually when building the query
> string:
>
>
>
>
> "Mark Williams" wrote:
>
> > There are three ways to specify the value of something that has the
> > uniqueidentifier datatype: using NEWID(), in string format, or in hex format.
> >
> > DECLARE @id1 uniqueidentifier
> > DECLARE @id2 uniqueidentifier
> > SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
> > SET @id2=0x2B8BC811D69F5E48B60A3E0F6839FFCf
> > --Believe it or not, @id1=@id2
> >
> > SELECT 1 WHERE @id1=@id2
> >
> > The only operators that are allowed between things of datatype
> > uniqueidentifier are comparison (<,>,=,>=,<=,<>). If you tried
> >
> >
> > DECLARE @id1 uniqueidentifier
> > DECLARE @id2 uniqueidentifier
> > DECLARE @colname nvarchar(15)
> > DECLARE @tblname nvarchar(15)
> > DECLARE @querystring nvarchar(512)
> > SET @colname=N'col1'
> > SET @tblname=N'table1'
> > SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
> > SET @id2=0x2B8BC811D69F5E48B60A3E0F6839FFCf
> >
> > SET @querystring=N'SELECT ' + @colname + N' FROM ' + @tblname + N' WHERE
> > id=' + @id1
> >
> > SELECT @querystring
> >
> > You get the error message:
> >
> > Server: Msg 403, Level 16, State 1, Line 13
> > Invalid operator for data type. Operator equals add, type equals
> > uniqueidentifier.
> >
> > So, you have to CAST the value for @id1 into a CHAR type to you can build
> > your query string:
> >
> > DECLARE @id1 uniqueidentifier
> > DECLARE @colname nvarchar(15)
> > DECLARE @tblname nvarchar(15)
> > DECLARE @querystring nvarchar(512)
> > SET @colname=N'col1'
> > SET @tblname=N'table1'
> > SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
> >
> > SET @querystring=N'SELECT ' + @colname + N' FROM ' + @tblname + N' WHERE
> > id=' +  CAST(@id1 AS nvarchar(512))
> >
> > This works, but look closely at the end of the WHERE condition in
> > @querystring:
> >
> > WHERE id=11C88B2B-9FD6-485E-B60A-3E0F6839FFCF
> >
> > You can't specify a query string this way; it must be quoted, like a string.
> > So, you have to add in the quote marks manually when building the query
> > string:
> >
> > DECLARE @id1 uniqueidentifier
> > DECLARE @colname nvarchar(15)
> > DECLARE @tblname nvarchar(15)
> > DECLARE @querystring nvarchar(512)
> > SET @colname=N'col1'
> > SET @tblname=N'table1'
> > SET @id1='11C88B2B-9FD6-485E-B60A-3E0F6839FFCF'
> >
> > SET @querystring=N'SELECT ' + @colname + N' FROM ' + @tblname + N' WHERE
> > id=''' +  CAST(@id1 AS nvarchar(512)) + N''''  --these are 4 single quotes
> >
> > SELECT @querystring
> >
> > Now the string is in the correct format for the query to execute.
> >
> > "janne" wrote:
> >
> > > But the table.id is a uniqueidentifier, how can the WHERE clause work
> > > if I convert the @id to CHAR?
> > >
> > > Mark Williams wrote:
> > > > The problem is that the uniqueidentifier datatype has a higher precedence
> > > > than the char types. When comparing or operating with two different
> > > > datatypes, the value with the lower precedence will be converted to the
> > > > datatype of the value with the higher precedence.
> > > >
> > > > See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp
> > > >
> > > > The solution here is to CAST the @id variable into a CHAR type, then build
> > > > your query string. That way you avoid the implicit conversion.
> > > >
> > > > "janne" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I'm trying to do something like this:
> > > > >
> > > > > CREATE PROCEDURE my_proc(
> > > > >    @id uniqueidentifier, @string varchar(100))
> > > > > AS
> > > > > EXEC('SELECT something FROM somewhere WHERE table.id =' +@id)
> > > > >
> > > > > I need to have it in a string because I'm using variables for
> > > > > "something" and "somewhere". It doesn't seem to like that I'm trying to
> > > > > put the uniqueidentifier in a string...
> > > > >
> > > > > How can I get around this?
> > > > >
> > > > >
> > >
> > >