getting values from sp_executesql

Posted on 2008-11-16
Last Modified: 2012-05-05

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

Question by:justin_smith
    LVL 11

    Accepted 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


    Author Closing Comment

    This is the best solution i got through EE ever.. it was so quick and spot on.. i really appreciate that
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    remarks: if you don't need dynamic sql, don't use dynamic sql!
    but I guess that you simplified your example in that regards :)

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now