Solved

Execute SQL

Posted on 2013-01-25
4
280 Views
Last Modified: 2013-01-25
I'm sure this is really easy but I just can't figure it out:

I have a stored procedure where I am building a sql statement dynamically, the sql statement returns a single value and I want to assign that returned value to a local variable for future use in the procedure:

Psuedo code:
declare @myValue varchar(10)
declare @sql  varchar(500)

Set @sql = 'SELECT col1 FROM ' + @tableName + ' WHERE x=y'
set @myValue = result of above sql statement

How is this acheived?
0
Comment
Question by:BrianFord
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38820787
use sys.sp_executesql which lets you pass parameters in and out like this
CREATE TABLE testTable (col1 VARCHAR(10), x INT, y INT);
INSERT INTO testTable VALUES ('Returned', 1, 1), ('not this', 1, 2);


declare @myValue varchar(10)
declare @sql  NVARCHAR(500)
DECLARE @tablename NVARCHAR(50) = 'testTable'

Set @sql = 'SELECT @myValue=col1 FROM ' + @tableName + ' WHERE x=y'

EXEC sys.sp_executesql @sql, N'@myValue varchar(10) OUT', @myValue OUT
--set @myValue = result of above sql STATEMENT

PRINT @myValue

DROP TABLE testTable;

Open in new window

0
 

Author Comment

by:BrianFord
ID: 38821117
Apologies, in my first post I said Stored Procedure but this is actualy a function and I cannot access temp tables in a function
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38821140
What temp tables, the testTable was just for the demonstration?  Did you try using just the dynamic sql part?
But I am not sure about dynamic sql inside a function, is it a table valued function?  I believe a scalar funtion has to be deterministic and would balk at dynamic sql.
Can you share a little more of your code and what you are trying to accomplish?  We only have what you have told us to go on and have to guess at details.
0
 

Author Closing Comment

by:BrianFord
ID: 38821187
OK, I think you just answered my question, I am using a Scalar function and it does indeed balk at the dynamic SQL.

I will re-think my logic and come up with a diffent way to achieve what I need

thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

24 Experts available now in Live!

Get 1:1 Help Now