TSQL Concatenation with uniqueidentifier type

I'm trying to assemble an insert statement inside a stored procedure:

The value of @move_guid is equal to the output of newid()

The procedure can compile without errors, but when I try to run it, I get:

Arithmetic overflow error converting expression to data type nvarchar.

How do I concatenate a uniqueidentifier into a string?
DECLARE @cmd nvarchar(max)
set @cmd='select * from move_request where guid=''' + cast(@move_guid as nvarchar) + ''' and completed=1'
execute(@cmd)

Open in new window

PMH4514Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in case you REALLY need dynamic sql, do it like this:
DECLARE @cmd nvarchar(max)
set @cmd='select * from move_request where guid= @guid and completed=1'
exec sp_Executesql @cmd, N'@guid uniqueidentifier' , @move_guid 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
don't use dynamic sql for that?


select * from move_request where guid= @move_guid and completed=1

Open in new window

0
 
PMH4514Author Commented:
I do need to use dynamic SQL - the query string is more complex than that built based on various search filters. I omitted all of that for simplicity.

Can you explain how your 2nd comment is working?  is sp_Executesql a system stored procedure that can take typecasting details as input or something?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Can you explain how your 2nd comment is working?  

yes. the first parameter of sp_executesql (which is a system procedure, so available in your sql server) is the sql statement, eventually having some @variables.

those needs to be defined in the second parameter: a nvarchar parameter holding all the parameter definititions. if you have more than 1 parameter to pass, see below example.

as from the third paramter, it's the values you want to pass, in the EXACT same order as they are defined in the second parameter.


DECLARE @cmd nvarchar(max)
set @cmd='select * from move_request where guid= @guid and completed= @b_completed '
exec sp_Executesql @cmd, N'@guid uniqueidentifier, @b_completed bit' , @move_guid , 1

Open in new window

0
 
PMH4514Author Commented:
Makes perfect sense. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.