• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

getting values from sp_executesql


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

1 Solution
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

justin_smithAuthor Commented:
This is the best solution i got through EE ever.. it was so quick and spot on.. i really appreciate that
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 :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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