andyknott
asked on
Stored Procedure EXEC @string get output
Hi,
This is sort of an on going thing I've been working out (with help from EE). I have a SP that needs to work with a dynamic table name which is passed in as a parameter. I've got this working in most cases but am stuck trying to return a variable from the executed string to the rest of the stored procedure. Previously I have managed to define a global cursor which is created in the EXEC statement but available to the rest of the SP.
How do you specify a global @variable?
I have code some thing like:
CREATE PROCEDURE someName
@tablePreifx varchar(50),
@Date as dateTime,
@needToGetThisOut nvarchar(50) output
SET @sSQL = '
SET @needToGetThisOut = (SELECT someColumn FROM [' + @tablePrefix + 'Bookings]
WHERE Date = @Date'
EXEC sp_executesql @sSQL, N'@Date datetime', @Date = @Date
SELECT @needToGetThisOut
With this code the @needToGetThisOut is null, it does hold the value it was assigned in the sSQL string.
I know that to use the @Date var dynamically (which is required in the real SP) in the sSQL string, it needed to be defined and passed in on the execute line.
I guess I need away to pass out the variable from the execute string, or make it global? How do I do either of these?
Thanks in Advance,
Andrew
This is sort of an on going thing I've been working out (with help from EE). I have a SP that needs to work with a dynamic table name which is passed in as a parameter. I've got this working in most cases but am stuck trying to return a variable from the executed string to the rest of the stored procedure. Previously I have managed to define a global cursor which is created in the EXEC statement but available to the rest of the SP.
How do you specify a global @variable?
I have code some thing like:
CREATE PROCEDURE someName
@tablePreifx varchar(50),
@Date as dateTime,
@needToGetThisOut nvarchar(50) output
SET @sSQL = '
SET @needToGetThisOut = (SELECT someColumn FROM [' + @tablePrefix + 'Bookings]
WHERE Date = @Date'
EXEC sp_executesql @sSQL, N'@Date datetime', @Date = @Date
SELECT @needToGetThisOut
With this code the @needToGetThisOut is null, it does hold the value it was assigned in the sSQL string.
I know that to use the @Date var dynamically (which is required in the real SP) in the sSQL string, it needed to be defined and passed in on the execute line.
I guess I need away to pass out the variable from the execute string, or make it global? How do I do either of these?
Thanks in Advance,
Andrew
ASKER
Hi,
I tried that, the parameter value does not get back to the command that called it.
Thanks
I tried that, the parameter value does not get back to the command that called it.
Thanks
Will this work for you?
SET @sSQL = 'SELECT someColumn FROM [' + @tablePrefix +'Bookings]
WHERE Date = ''' + convert(char(10),@Date,101
EXEC sp_executesql @sSQL
SET @sSQL = 'SELECT someColumn FROM [' + @tablePrefix +'Bookings]
WHERE Date = ''' + convert(char(10),@Date,101
EXEC sp_executesql @sSQL
ASKER
That would work in the example above - but not in the real SP which i did not post as it is long and complicated. The actual one uses the value of @Date dynamically. Also it would not help me get the output var out.
Is there away to pass a param out, just like I pass the @date in. Or a global @var? I've done something similar with a global cursor - created it in an executes string but used it in the rest of the SP afterwards.
Thanks
Is there away to pass a param out, just like I pass the @date in. Or a global @var? I've done something similar with a global cursor - created it in an executes string but used it in the rest of the SP afterwards.
Thanks
Will this work for you?
SET @sSQL = 'SELECT someColumn FROM [' + @tablePrefix +'Bookings]
WHERE Date = ''' + convert(char(10),@Date,101
EXEC sp_executesql @sSQL
SET @sSQL = 'SELECT someColumn FROM [' + @tablePrefix +'Bookings]
WHERE Date = ''' + convert(char(10),@Date,101
EXEC sp_executesql @sSQL
Whoops I didn't mean to do that. Darn refresh button. Hmm. I am not sure how you can get around it. I have never had a case with the output parameter. Sorry.
ASKER
Thanks anyway,
Hopefully someone will have an idea.
Hopefully someone will have an idea.
Is what you want just to set a parameter via sp_executesql?
declare @i int
exec sp_executesql N'select @i = 999', N'@i int output', @i output
select @i
To get the return from an SP call
declare @OutputParameter varchar(100) ,
@error int ,
@SPName varchar(128) ,
@SPCall nvarchar(128) ,
@rc int
select @SPCall = 'exec ' + @SPName + ' @OutputParameter output'
exec @rc = sp_executesql @SPCall, N'@OutputParameter varchar(100) output', @OutputParameter output
select @Error = @@error
declare @i int
exec sp_executesql N'select @i = 999', N'@i int output', @i output
select @i
To get the return from an SP call
declare @OutputParameter varchar(100) ,
@error int ,
@SPName varchar(128) ,
@SPCall nvarchar(128) ,
@rc int
select @SPCall = 'exec ' + @SPName + ' @OutputParameter output'
exec @rc = sp_executesql @SPCall, N'@OutputParameter varchar(100) output', @OutputParameter output
select @Error = @@error
ASKER
Hi nigelrivett,
Thanks for the response.
declare @i int
exec sp_executesql N'select @i = 999', N'@i int output', @i output
select @i
This looks like exactly what i need. I can't get it to work though. I have many inputs (like @Date) in my example that get passed in. What would the exec sp_executesql line look like with inputs as well?
Currently my exec statement looks something like -
EXEC sp_executesql @tempStr, N'
@Time dateTime,
@Name nvarchar(60),
@No_Of_Guests tinyint,
@User_ID int,
@Booked_By_User bit,
@Special_Text nvarchar(200),
@Pay_Scheme nvarchar(60),
@Time_Created dateTime',
@Time = @Time,
@Name = @Name,
@No_Of_Guests = @No_Of_Guests,
@User_ID = @User_ID,
@Booked_By_User = @Booked_By_User,
@Special_Text = @Special_Text,
@Pay_Scheme = @Pay_Scheme,
@Time_Created = @Time_Created
Thanks
Thanks for the response.
declare @i int
exec sp_executesql N'select @i = 999', N'@i int output', @i output
select @i
This looks like exactly what i need. I can't get it to work though. I have many inputs (like @Date) in my example that get passed in. What would the exec sp_executesql line look like with inputs as well?
Currently my exec statement looks something like -
EXEC sp_executesql @tempStr, N'
@Time dateTime,
@Name nvarchar(60),
@No_Of_Guests tinyint,
@User_ID int,
@Booked_By_User bit,
@Special_Text nvarchar(200),
@Pay_Scheme nvarchar(60),
@Time_Created dateTime',
@Time = @Time,
@Name = @Name,
@No_Of_Guests = @No_Of_Guests,
@User_ID = @User_ID,
@Booked_By_User = @Booked_By_User,
@Special_Text = @Special_Text,
@Pay_Scheme = @Pay_Scheme,
@Time_Created = @Time_Created
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY. Moderators Computer101 or Netminder will return to finalize these if still open in seven days. Please post closing recommendations before that time.
Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response. This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> You cannot delete a question with comments, special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process for further information, if needed. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site. If you are an EE Pro user, use the Power Search option to find them. Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why. In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why. This helps all involved, as well as future persons who may access this item in the future to seek help.
To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20131980.html
https://www.experts-exchange.com/questions/Q.20190186.html
https://www.experts-exchange.com/questions/Q.20265539.html
https://www.experts-exchange.com/questions/Q.20276911.html
https://www.experts-exchange.com/questions/Q.20277661.html
PLEASE DO NOT AWARD THE POINTS TO ME.
------------> EXPERTS: Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days. Also, if you are interested in the cleanup effort, please click this link https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made. Expert input is always appreciated to determine the fair outcome.
Thank you everyone.
Moondancer
Moderator @ Experts Exchange
Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response. This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> You cannot delete a question with comments, special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process for further information, if needed. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site. If you are an EE Pro user, use the Power Search option to find them. Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why. In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why. This helps all involved, as well as future persons who may access this item in the future to seek help.
To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20131980.html
https://www.experts-exchange.com/questions/Q.20190186.html
https://www.experts-exchange.com/questions/Q.20265539.html
https://www.experts-exchange.com/questions/Q.20276911.html
https://www.experts-exchange.com/questions/Q.20277661.html
PLEASE DO NOT AWARD THE POINTS TO ME.
------------> EXPERTS: Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days. Also, if you are interested in the cleanup effort, please click this link https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made. Expert input is always appreciated to determine the fair outcome.
Thank you everyone.
Moondancer
Moderator @ Experts Exchange
Zero response, finalized by Moondancer - EE Moderator
What I am talking about is take this part and go like this
....
WHERE Date = @Date'
and go like
...
WHERE Date = @Date
SELECT @needToGetThisOut '