CODEWITHSUNDEEP
t-sql
Nick LaMarca
Nick LaMarca

Reputation: 8188

Adding GUID parameter to a sql string

I am having complications adding a guid type to a sql string and getting the query to go. I have to write the sql this because I need to pass the name database as a parameter.

The following code is what I'm trying to do but it doesnt work. No matter what I do it doesn't read the GUID correctly. It gives me the following error when executing the stored proc. Probably because guid has "-" in them

Incorrect syntax near '-'.

Here's the code:

ALTER PROCEDURE [dbo].[templatesAndBaskets_Select]
 @guid uniqueidentifier,
 @DatabaseName varchar(50)
AS
BEGIN
Declare @sql varchar(max)

Set @sql=

'select soldtoid 
from '    +  @DatabaseName + '.templatesAndBaskets' +
' where ordergroupid = ' +  CONVERT(varchar(max),@guid)

print @sql
EXECUTE SP_EXECUTESQL @sql
END

Upvotes: 4

Views: 11430

Answers (2)

marc_s
marc_s

Reputation: 754348

You probably just simply need to put single quotes around the GUID :

SET @sql =
   N'SELECT soldtoid FROM ' +  @DatabaseName + N'.templatesAndBaskets' +
   N' WHERE ordergroupid = ''' +  CONVERT(nvarchar(max), @guid) + N''''

That should result in a SQL statement something like this:

SELECT soldtoid FROM database.templatesAndBaskets
WHERE ordergroupid = '5E736CE7-5527-40ED-8499-2CA93FC7BC9C'

which is valid - yours without the single quotes around the GUID isn't valid...

Update: you also need to change your @sql variable to NVARCHAR for sp_Executesql - try this:

ALTER PROCEDURE [dbo].[templatesAndBaskets_Select]
    @guid uniqueidentifier,
    @DatabaseName NVARCHAR(50)
AS
BEGIN
   DECLARE @sql NVARCHAR(MAX)

   SET @sql =
       N'SELECT soldtoid FROM ' +  @DatabaseName + N'.templatesAndBaskets' +
       N' WHERE ordergroupid = ''' +  CONVERT(nvarchar(max), @guid) + N''''

   PRINT @sql
   EXECUTE sp_ExecuteSQL @sql
END

Does that work??

Upvotes: 7

CristiC
CristiC

Reputation: 22698

Put the @guid between '

ALTER PROCEDURE [dbo].[templatesAndBaskets_Select]
 @guid uniqueidentifier,
 @DatabaseName varchar(50)
AS
BEGIN
Declare @sql varchar(max)

Set @sql=

"select soldtoid from "    +  @DatabaseName + ".templatesAndBaskets" +
" where ordergroupid = '" +  CONVERT(varchar(max),@guid)+"'"

print @sql
EXECUTE SP_EXECUTESQL @sql
END

Upvotes: 0

Related Questions