Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Execute SQL

Posted on 2013-01-25
4
Medium Priority
?
323 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
[X]
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
  • 2
  • 2
4 Comments
 
LVL 27

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 27

Accepted Solution

by:
Chris Luttrell earned 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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