Search This Blog

Return a value from a dynamic sql

For beginners, this is one of the toughest jobs, Here is an example, hope this will help u guys :)

Use Northwind

CREATE PROC TEST(
@DATABASENAME VARCHAR(30),
@COUNTOUT INT OUTPUT
)
AS
BEGIN
DECLARE @v_SQL nVARCHAR(4000)
DECLARE @COUNT INT


SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'

EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT
SET @COUNTOUT=@COUNT

RETURN @countout
END

4 comments:

b u d d h a said...

good one

timw said...

Just to let know that this article was useful to me today. Big thnx.

27 Sep 2011

bourgon said...

There's a joke here that beginners shouldn't be using dynamic SQL without knowing all the pitfalls and Dire Implications.

But thanks for posting this. :)

Colin Ng said...

Thanks! This was really useful!