How to set @localvariable in dynamically created SQL statement? 1

[uerobertson]

I am trying to write a stored procedure that will get a colour description from a table (SQL Server 2000). The procedure has to be able to get info from several different tables (supplied by our clients). The only assumption is that the client table has the two fields and that both are varchar. The colour code is only three chars so varchar(50) is just overkill.

Input:
name of a table,
name of colour description field,
name of colour code field,
colour code (for which we need the description)

Output: colour description

...

CREATE PROCEDURE dbo.insertColourDescription 
	@descTableName AS varchar(50),
	@descFieldName AS varchar(50),
	@colourFieldName AS varchar(50),
	@NRFColourCode AS varchar(50)
AS  	

DECLARE @description AS nvarchar(255)
DECLARE @SQL AS nvarchar(255)

SET @SQL = 
'SELECT @description = (SELECT [' + @descFieldName + '] FROM ' + @descTableName + ' WHERE ' + '[' + @colourFieldName + '] = "'+ @NRFColourCode + '")'

EXEC (@SQL)

GO

...

When I execute the procedure it produces the error:
Must declare the variable '@description'.

A print of @SQL produces the following:
SELECT @description = (SELECT [Colour] FROM Results WHERE [Colour Code] = "001&quot

This SQL works perfectly fine when I run it on it's own (have to switch the " to ' of course).

How do I execute this piece of dynamic SQL?

Any help will be greatly appreciated.

Ursula.

 

[zhavic]

Use sp_executesql

EXECUTE sp_executesql @SQL,
N'@description nvarchar(255) OUTPUT',
@description OUTPUT


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[uerobertson]

Thanks - that did the trick.

Ursula