assign from execute sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

assign from execute sp_executesql

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-10 : 12:19:13

Hi,
This returns a single date.

execute sp_executesql @sql

How can I assign the returned value to a variable such as:
declare @DateOfData = execute sp_executesql @sql

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 12:22:37

From your friend, Books online (BOL)



DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-10 : 12:24:33

Thanks but I can not use your sample code to solve my sql I sent.
Any thoughts please?

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 13:14:37

Use an OUTPUT variable

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 13:15:48

What's the content @sql ?

its rare to see sp_executesql with only the first parameter used, as its real power is in using parameter 2 (for definition of additional parameters) and then a list of those additional parameters

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 13:17:31

I personally wouldn't use the return value for an output value. Better to reserve that to indicate Success (zero) or Failure/Error (non-zero)

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 13:18:38

Whatever I thought I was replying to has evaporated! Pity as it had the Cut&paste from BoL with the definition of the OUTPUT parameter ... which I can't be arsed to go look up (sorry!)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 05:58:51

Solved. Thankyou

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources