Solved

Execute SQL

Posted on 2013-01-25
4
300 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 36
Show Results for Latest DateTime in a View 27 25
SQL server vNext 18 29
SQL2016 to ORACLE11G linked-server 6 15
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 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