January 14, 2008 at 2:10 pm
The Dynamic SQL works for me in a stored procedure, but I have problem in outputting the result through the Stored Proc output parameters..
Part of my code in the stored procedure is as follows:
DECLARE @SQL varchar(1000), @TabName varchar(500), @Col1Name varchar(500), @Col2Name (varchar500)
Select @TabName = TabName, @Col1Name = Col1Name, @Col2Name = Col2Name
From TabColName WHERE ID = @inputparam1
(TabColName is the table where I keep the table names and their associated column names)
@SQL = 'SELECT ' + @Col1Name + ', ' + @Col2Name + ' FROM ' + @TabName + ' WHERE TabID = ' + @inputparam2
EXEC sp_executeSQL @SQL
The above SQL statement works fine, but the question is: How do I return the values of Col1Name and Col2Name as retrieved from the table indicated by @TabName? I tried the statement:
@SQL = 'SELECT ' + @Output1 + '= ' + @Col1Name + ', ' + @Output2 + '= ' + @Col2Name + ' FROM ' + @TabName + ' WHERE TabID = ' + @inputparam2
EXEC sp_executeSQL @SQL
but it doesn't work (syntax error!).
Thanks very much for any suggestions and advises.
sg2000
January 14, 2008 at 2:21 pm
I haven't had to solve this before, so there might be a better solution, but here's a suggestion that might work.
In the calling procedure (the one that builds the SQL statement), create a global temporary table (one that starts with "##"), and have the dynamic SQL insert into that table. Then, once the dynamic SQL is done, select from that table.
Note: I haven't tried this. Please let me know if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2008 at 2:29 pm
sg - why don't you just print the @SQL variable so you can see where the syntax error is?
PRINT @SQL
would do the trick....If you don't see it - post the code here so we can take a crack at it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 7:00 am
You can use parameters within sp_executesql. Those parameters can be output parameters. You can set those values within the ad hoc query and output them from there and then set them to the parameter values that are output in your stored procedure. There's even an example of this in books online for sp_executesql.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2008 at 8:30 am
Parameterizes sp_executesql! Of course! (I knew I was saying something stupid in my prior post on this one.) Yeah, do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 12:49 am
To All:
Thanks very much for the suggestion of using sp_executeSQL with parameters. I tried the following:
DECLARE @SQL varchar(1000), @Param varchar(500)
SET @param = '@Output varchar(50) OUTPUT'
SET @SQL = 'SELECT @Output =' + @Col1name FROM Table WHERE ID = 123
EXEC sp_ExecuteSQL @SQL, @param
Note: @Col1Name contains the column name retrieved from another table.
I got the following error when executing the sp:
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."
Any idea what this is? Thanks in advance for any suggestions.
sg2000
January 16, 2008 at 2:22 am
It worked simply because you corrected your syntax....
There was a logical error in your string....you tried to assign the value of a varialbe to the value of another...which is not possible.....while building a string, you should keep in your mind that, if you concatenate a variable outside quote, the string will assume its value.....to treat it as a variable, you need to put it in quote....I mean to say the way you assigned the value to the output variable......check....and It was a great suggestion from SSCrazy, that you should print your string before you execute it.....it is probably then that you corrected your syntax.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
January 16, 2008 at 6:57 am
There's a nice tutorial for using sp_executesql with output parameters here[/url].
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 16, 2008 at 11:37 am
I actually had to solve this kind of problem a couple of weeks ago. I was modifying some package type software that always sends string type 'WHERE' clauses to its report stored procedures, which are used by Crystal Reports.
In this case I analyzed the output of the procedure to get the proper columns and data types. Then created a temp table in the procedure. Just before the SQL statement was executed I put an INSERT INTO the temp table with all of the columns into another string - @SQLI. This was executed as:
EXEC (@SQLI + @SQL).
This worked fine and I had a temp table I could tinker with before returning it to the report.
Todd Fifield
January 16, 2008 at 12:39 pm
try this:
DROP TABLE [#tempTable]
SELECT CAST('hello' AS VARCHAR(128)) AS dbname,*
INTO #tempTable
FROM sys.all_objects
WHERE 1 = 0
INSERT INTO [#tempTable]
EXEC sp_MSforeachdb 'USE ?;SELECT ''?'' as dbname,* FROM sys.all_objects WHERE NAME LIKE ''%cycle%'''
January 17, 2008 at 11:53 am
Thank you all for the suggestions. I tried them but still encounter problem. Even with the most simple example as listed in the tutorial encoutered error. My SP code is as follows:
DECLARE @SQL nvarchar(1000), @OutputDef nvarchar(1000)
SET @SQL = 'SELECT @val = count (*) FROM TableA WHERE ID = 3 '
SET @OutputDef = '@Val int OUTPUT'
EXEC sp_executeSQL @SQL,@OutputDef
PRINT @SQL
This is the error I got:
The parameterized query '(@Val int OUTPUT)SELECT @val = count (*) FROM TableA WHERE ' expects the parameter '@Val', which was not supplied.
SELECT @val = count (*) FROM TableA WHERE ID = 3
The SQL in the PRINT output looks OK to me. So where is the error?
Please help. Thanks in advance.
sg2000
January 17, 2008 at 12:05 pm
I usually have to declare and pass in a variable to get something back
so - this works for me:
DECLARE @SQL nvarchar(1000), @OutputDef nvarchar(1000),@val int
SET @SQL = 'SELECT @val = count (*) FROM tableA '
SET @OutputDef = '@Val int OUTPUT'
EXEC sp_executeSQL @SQL,@OutputDef,@val output
--@val now has the output value.
PRINT @SQL+' -- '+cast(@val as varchar)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply