How to execute a query through Exec and get result

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 2005 Forums

Transact-SQL (2005)

How to execute a query through Exec and get result

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-08-13 : 06:03:04

I have a query
Select EmpSalary from Employee where EmployeeCode=1

and I want to have the resultant in a variable BUT i want to use the EXEC because the above mentioned query is dynamically created and passed from an outer SP.

Can any body help me

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 06:36:46

If you must do it this way you would have to use sp_executeSql,

Brief example


DECLARE @sql NVARCHAR(4000)
DECLARE @outputVariable INT -- Change to required datatype

SET @sql = 'SELECT @output = Employee where EmployeeCode=1'

EXEC sp_executeSql @sql, N'@output INT OUTPUT', @outputVariable OUTPUT

However, as you are not making up your query (it's getting returned from proc?)

You'd have to do something like


< You declerations -- I am assuming you are assigning the output of your stored proc to a variable called @sql which is of type NVARCHAR - this is important)>

DECLARE @outputVariable INT -- Change this to the datatype that you are after

SET @sql = REPLACE(@sql, 'SELECT', 'SELECT @output = ')

EXEC sp_executeSql @sql, N'@output INT OUTPUT', @outputVariable OUTPUT

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 06:39:51

In the example above, I am assuming that your proc returns exaclty the format you specified = 1 select statement in your format.

The REPLACE changes the string to include the @output variable. So given the string


Select EmpSalary from Employee where EmployeeCode=1

It will turn this into


SELECT @output =  EmpSalary from Employee where EmployeeCode=1

Then you will use this in sp_executeSql. If you haven't used this call before I would check books on line. sp_executeSql takes 3 parameters,

1) The statement (@Sql variable)

2) A list of comma seperated variables that will be unique to the scope of the call (@output INT OUTPUT) -- you will need to change this to suit. These must be provide as NVARCHAR (note the N' start to the quoted string)

3) A list of variables for substitution. In this case @outputvariable gets substituted to @output in the dynamic scope. Note the OUTPUT -- if you don't use this you won't get any results

In fact. Check out http://www.sommarskog.se/dynamic_sql.html for a comprehensive and exceedingly useful guide to dynamic sql in general.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources