Dynamic SQL Question

[ccscott]

Total newbie question, but i'm having some trouble executing the following dynamic sql statement. The folowing represents an example of the piece of code...

CREATE TABLE #Temp([VID] INT)
INSERT INTO #Temp VALUES(6565)
ALTER TABLE #Temp ADD Counter INT

DECLARE @SQL VARCHAR(250)
DECLARE @VID INT
DECLARE @COUNTERVAR INT

SET @VID='6585'
SET @COUNTERVAR=1

SET @SQL = 'UPDATE #Temp SET COUNTER = '+ @COUNTERVAR +' WHERE VID='+ @VID + ''
EXEC (@SQL)


Below is the error message that I am receiving

Conversion failed when converting the varchar value 'UPDATE #Temp SET COUNTER = ' to data type int.

 

[RiverGuy]

You don't need dynamic sql in this case. The following will run fine:

CREATE TABLE #Temp([VID] INT)
INSERT INTO #Temp VALUES(6565)
ALTER TABLE #Temp ADD Counter INT

DECLARE @SQL VARCHAR(250)
DECLARE @VID INT
DECLARE @COUNTERVAR INT

SET @VID='6585'
SET @COUNTERVAR=1

UPDATE #Temp SET COUNTER =  @COUNTERVAR  WHERE VID = @VID

 

[ccscott]

RiverGuy - Thanks; however i was just using this as an example for the sake of simplicity. I'm getting the same error when attempting to execute dynamic sql in a much larget block of code.

 

[Qik3Coder]

Man i wish i had that color coder...

You have do 2 ' inside the string in order to get 1, you were close.

A trick when working with dynamic SQL is to Print it, then exec the printed results to see the REAL error

CREATE TABLE #Temp([VID] INT)
INSERT INTO #Temp VALUES(6565)
ALTER TABLE #Temp ADD Counter INT

DECLARE @SQL VARCHAR(250)
DECLARE @VID INT
DECLARE @COUNTERVAR INT

SET @VID='6585'
SET @COUNTERVAR=1

SET @SQL = [red]'UPDATE #Temp SET COUNTER = '[/red]+ @COUNTERVAR +[red]' WHERE VID='''[/red]+ @VID + [red]'''[/red]
PRINT @SQL
EXEC (@SQL)

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[RiverGuy]

You need to cast your INTs to a string datatype. Try the following:

CREATE TABLE #Temp([VID] INT)
INSERT INTO #Temp ([VID]) VALUES(6565)
ALTER TABLE #Temp ADD Counter INT

DECLARE @SQL VARCHAR(250)
DECLARE @VID INT
DECLARE @COUNTERVAR INT

SET @VID='6565'
SET @COUNTERVAR=1

SET @SQL = 'UPDATE #Temp SET COUNTER = '+ CONVERT(VARCHAR(4), @COUNTERVAR) +' WHERE VID='+ CONVERT(VARCHAR(4), @VID) + ''
PRINT @SQL
EXEC (@SQL)
SELECT * FROM #Temp
DROP TABLE #Temp

 

[ccscott]

Thanks Guys!!!