August 7, 2008 at 9:00 am
I have two databases on two different servers that I need to write a function to merge.
I've just about got it, but there's a major difference between version 5 and version 6 of the databases, and I need to test for that.
As a bit of test code, I created the following... the idea was to create a temporary stored procedure via dynamic SQL and have it return the version selected out of the version database on the source server.
My test case looks like this
declare @version int
declare @serverid varchar(100)
declare @cmd nvarchar(max)
set @serverid = 'S3-SQL1' /*Sets the server to pull from... this will be a variable in a cursor that pulls all the records from one server first, then the second server second. */
set @cmd = N'CREATE PROCEDURE ##tmpverfinder AS declare @version int SELECT @version = '
set @cmd = @cmd + N'majorversion FROM [' + @serverid + '].asfaxingsql.dbo.version '
set @cmd = @cmd + N'RETURN @version'
EXEC sp_executesql @cmd --Creates the temp sproc ##tmpverfinder
EXEC @version = sp_executesql N'##tmpverfinder' --should return the version to @version
DROP PROCEDURE ##tmpverfinder --cleans up ##tmpverfinder
Print @version --Should return a 6... returns a 0
The sproc is created... but it keeps returning 0. If I change the return to a print... it prints a 6... can the return statement not return the value of a variable?
August 7, 2008 at 9:13 am
Nm took a simpler route... made a Sproc that reads the version and returns a 1 for 6+ and a 0 for 5 or less... that works fine.
Thanks anyways.
August 7, 2008 at 9:14 am
I have no idea why the PRint returns a 6, but the select should return a 0 because sp_executesql completed successfully. If you want to return the return value from your temporary sp you will need to change the sp_executesql. To something like this:
[font="Courier New"]DECLARE @sql NVARCHAR(MAX), @params NVARCHAR(100), @RetVal INT
DECLARE @version INT
DECLARE @serverid VARCHAR(100)
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = N'CREATE PROCEDURE ##tmpverfinder AS declare @version int Set @version = 1 '
SET @cmd = @cmd + N'RETURN @version'
EXEC sp_executesql @cmd --Creates the temp sproc ##tmpverfinder
SET @params = '@Version Int Output'
SET @sql = 'Exec @version = ##tmpverfinder'
EXEC @RetVal = sp_executesql @sql, @Params, @version = @version Output
DROP PROCEDURE ##tmpverfinder --cleans up ##tmpverfinder
-- version should be 1, Retval should be 0
SELECT @version AS version, @RetVal AS return_from_sp_executesql
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 12:23 pm
Jack Corbett (8/7/2008)
I have no idea why the PRint returns a 6, but the select should return a 0 because sp_executesql completed successfully. If you want to return the return value from your temporary sp you will need to change the sp_executesql. To something like this:
Ok what if I need to pass something into the temproary stored procedure? I've worked with dynamic SQL but not at the level of abstraction I'm at now... and I'm completely drawing a blank.
Say for instance if I had to pass @serverid to the temporary stored proc and then pass back the return code from the temporary stored proc back to the calling SQL block?
August 7, 2008 at 12:43 pm
If you need to pass parameters into the temporary procedure you just add those parameters to @params and add the parameter the @sql being called and to the parameters added at the end. Like this:
[font="Courier New"]DECLARE @sql NVARCHAR(MAX), @params NVARCHAR(100), @RetVal INT
DECLARE @version INT
DECLARE @serverid VARCHAR(100)
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = N'CREATE PROCEDURE ##tmpverfinder (@Serverid int) AS declare @version int Set @version = 1 '
SET @cmd = @cmd + N'RETURN @version'
EXEC sp_executesql @cmd --Creates the temp sproc ##tmpverfinder
SET @params = '@Version Int Output, @ServerId int'
SET @sql = 'Exec @version = ##tmpverfinder @ServerId'
EXEC @RetVal = sp_executesql @sql, @Params, @version = @version Output, @ServerId = 1
DROP PROCEDURE ##tmpverfinder --cleans up ##tmpverfinder
SELECT @version[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 2:18 pm
The only way to pass something back from Dynamic SQL is through a Table: temporary or permanent (table vars will not work).
You can pass limited info back through an Error Signal also, but that's a hack (unless you really do have an error).
(Actually, there is another way, rarely ever used: using SET CONTEXT_INFO, but I'd advise against that)
The recommended and generally used way is through Temp Tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply