Table Column as Variable
-
David,
In the interest of understanding dynamic sql better and its weird syntax, if you were to write this as a normal stored procedure what would it look like?
-
...if you were to write this as a normal stored procedure what would it look like... You cannot, that is why it is dynamic
The only way I can see to describe this, is as follows
You create a very simple procedure thus
CREATE PROCEDURE usp_test
@result varchar(255) OUTPUT
AS
SELECT @result = [column] FROM
WHERE [where clause]
GO
Then you decide that the column name can be variable (the dynamic bit) so you would change the proc to this
CREATE PROCEDURE usp_test
@colname varchar(255),
@result varchar(255) OUTPUT
AS
DECLARE @sql nvarchar(300)
SET @sql = 'SELECT @result = ' + @colname + ' FROM
WHERE [where clause]'
EXEC sp_executesql @sql, N'@result varchar(255) OUTPUT', @result OUTPUT
GO
To use the first proc
DECLARE @result varchar(255)
EXEC usp_test @result OUTPUT
This will always return the value of the column [column]
To use the second proc
DECLARE @result varchar(255)
EXEC usp_test 'columnname',@result OUTPUT
This will return the value of the column specified
Sorry if this looks noddy but I was not sure what answer you was looking for
Far away is close at hand in the images of elsewhere.
Anon. -
David,
On the contrary, every exchange I have on the subject of dynamic panels makes it become clearer.
But if instead of the internal variable @result I were to use @res (just to be clearer), why does the third parameter of sp_executesql now have to be @res=@result OUTPUT. It would seem more intuitive to have to write @result=@res OUTPUT. Is it the fact that you have the OUTPUT clause that requires you to reverse them?
CREATE PROCEDURE usp_test
@colname varchar(255),
@result varchar(255) OUTPUT
AS
DECLARE @sql nvarchar(300)
SET @sql = 'SELECT @res = ' + @colname + ' FROM
WHERE [where clause]'
EXEC sp_executesql @sql, N'@res varchar(255) OUTPUT', @res=@result OUTPUT
GO
-
The syntax is incorrect, but seems to work
The correct syntax should be
EXEC sp_executesql @sql, N'@res varchar(255) OUTPUT', @result OUTPUT
Corresponding names in the parameter list and the list of parameters/contants is not important (but would avoid confusion if they were the same
), only the ordinal position is.Far away is close at hand in the images of elsewhere.
Anon. -
So if I have two variables to manage then I would have
EXEC sp_executesql @sql, N'@res1 varchar(255) OUTPUT,@res2 varchar(255) OUTPUT', @result1 OUTPUT, @result2 OUTPUT
and this would cause @res1 to return in @result1 and @res2 in @result2?
I can't quite remember where I found my earlier syntax. But one thing is for sure, it confuses the hell out of me.
-
Correct
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply