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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy