Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Execute SQL

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

840 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