[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Execute SQL

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
BrianFord
Asked:
BrianFord
  • 2
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
BrianFordAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
BrianFordAuthor Commented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now