Dynamic query problem
Is it possible to return a declared variable value from a dynamic query. For example using the following code extract...
BEGIN
SET @cmd =
'SELECT ' + CONVERT(nvarchar,@CloumnName) +
' AS colourCost, 0 AS runOnCost ' +
' FROM [user].' + CONVERT(nvarchar,@tableName) +
' WHERE quantity=' + CONVERT(nvarchar,@quantity)
SET NOCOUNT OFF
EXEC(@cmd)
END
I would like to achieve the following.....
BEGIN
SET @cmd =
'SELECT ' + CONVERT(nvarchar,@CloumnName) +
' AS @GetValue = colourCost, 0 AS runOnCost ' +
' FROM [user].' + CONVERT(nvarchar,@tableName) +
' WHERE quantity=' + CONVERT(nvarchar,@quantity)
SET NOCOUNT OFF
EXEC(@cmd)
END
I have tried various approaches using temporary tables,
I am aware of the benifits of using sp_executesql.
If there is a solution to this please let me know.
|
