Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

getting values from sp_executesql

HI

It is something similar to this i am trying to do

declare @query nvarchar(500) ,
            @value int

set @query ='select count(*) from table_a'

i want to assign the count to @value  with sp_executesql

How can i do this

Thanks
0
justin_smith
Asked:
justin_smith
1 Solution
 
Louis01Commented:
Use the OUTPUT parameter. (Sample from SQL books online below.)
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);
 
SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
 
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

Open in new window

0
 
justin_smithAuthor Commented:
This is the best solution i got through EE ever.. it was so quick and spot on.. i really appreciate that
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remarks: if you don't need dynamic sql, don't use dynamic sql!
but I guess that you simplified your example in that regards :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now