SP + exec

[vlitim]

I have the below SQL statement which is being built up in the SP. What I need to do now is assign the result to a variable, but how do you do this when you need to execute the @sql??

SET @SQL='SELECT id FROM table WHERE id='+@id+' AND name='+@name+''


SET @var = exec(@sql)

which doesn't work??

 

[JamesLean]

You don't need to use dynamic SQL when using variables in place of expressions. So you can do:

DECLARE @var int

SELECT @var = id
FROM table
WHERE id = @id
  AND name = @name

Actually, this example is nonsense anyway as you already have the value of id in @id!! But this should give you the idea.

--James

 

[vlitim]

yeah but in this case the SQL statement has to be dynamic because I am passing the SP a comma del list which I am splitting up and creating the @SQL statement from! (didn't really make that clear in the last post!)

 

[rostler]

If the only reason you're using dynamic SQL is because of the comma delimited list, you could delegate parsing the list to a user defined function. The udf should convert your delimited list into a temporary table which you can use in a normal table join - this removes the need to build dynamic sql.

e.g.

SELECT myTable.myField, myTable.otherField
FROM myTable.myTable INNER JOIN udf_list_to_table(myList) udf
ON myTable.myField = udf.myField

In this case the table returned by the udf has a field called myField which is the same type as myTable.myField

HTH

Richard

 

[JamesLean]

OK, lets say you want to find the id of a person with a particular name (which you'll pass in) and assign this to a variable. You can do this using the sp_executesql stored proc, like this:

DECLARE @name varchar(20)
SET @name = 'James'

DECLARE @cmd nvarchar(200)
SET @cmd = 'select @id = id from mytable where name = ''' + @name + ''''

DECLARE @var int

EXEC sp_executesql @cmd, N'@id int OUTPUT', @var OUTPUT

PRINT 'ID: ' + CONVERT(varchar(20), @var)

--James