How to set value from SP into variable? 1

[EugenePaliev]

Hello all!

There are two things I want to do:
1) to know number of records
2) set this number into variable.

It works if I do something like this

declare @rows int
set @rows = (select count(column_name) from table where column_name2=x)

But I need to build my sql statement dinamicly. I use sp_executesql to accomplish this. So I try next

declare @rows int
declare @sql nvarchar(1000)
set @sql = N'some select statement here ... '
set rows = (execute sp_executesql @statement)

and it doesn't wotk... "Incorrect syntax near the keyword 'execute'."

Is there a way to dynamicly execute sql (with sp_executesql) and set returned value into variable?

Any help would be greatly appreciated!!
Thanks in advance. Good Luck!

 

[tlbroadbent]

Like other stored procedures, sp_executesql can return values in OUTPUT parameters. However, you have to use the correct syntax.
[tt]
declare @rows int, @sql nvarchar(1000)

--Create the SQL statement
--Note that @p1 is used to return the count
set @sql =
N'Select @p1=count(*)
from table where column_name2=''x'''

--Return value of output parameter @p1 to @rows
--sp_executesql arguments
--1) The Sql statement
--2) The parameter declaration
-- (can include multiple params)
--3) A parameter value
-- (one for each param declared)

Exec sp_executesql @sql,N'@p1 int Output',@p1=@rows output

--Print the returned value
Print @rows[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[EugenePaliev]

Thank you Terry!

I will try it a bit later and write back how lucky I am with it.

++ for you (and thanks again)! Good Luck!