-
Passing variable from sp_executesql
I am trying to execute a certain command using sp_executesql function available in MSSQL, but I somehow need to return a 'result' code back to the calling procedure. I understand from MSSQL's help that the commands in sp_executesql are sent to the server as a separate batch, and as such cannot directly reference calling procedure's variables.
I was wondering if there is a way of returning such 'result' code without having to physically write it to some location in the DB (which the calling procedure would then read).
Any help on this would be greatly appreciated.
-
please post an Example on what you are trying to do.
-
How about creating a temporary table, writing to the table with sp_executesql and reading the table after that.
-
Is it a simple result code? In this case it is possible, ex:
CREATE PROCEDURE subproc AS
RETURN 77
GO
CREATE PROCEDURE mainproc AS
DECLARE @iRetCode Int
execute @iRetCode = subproc
SELECT @iRetCode
GO
execute mainproc
GO
DROP PROCEDURE mainproc
GO
DROP PROCEDURE subproc
GO
Last edited by andi_g69; 07-11-2005 at 01:26 PM.
-
ofcourse your example works fine because there is no sp_executesql. please give an example on what you are trying to achieve using sp_executesql because there are alternatives.
Alternatives are temptable, openquery, using OUTPUT variables. etc.
-
The code which sp_executesql will process is, in itself, not that important as it will (in my case) vary. A simple example below simply reads a table, based on which it sets a value to a return variable. It is this return variable that I somehow need to pass back to the calling procedure:
Code:
CREATE PROC MyProcedure AS
DECLARE @CMD NVARCHAR(4000)
SET @CMD = N'DECLARE @RET INT ' + N'SELECT @RET = CASE MyField WHEN 'Value X' THEN 1 WHEN 'Value Y' THEN 2 END FROM MyTable'
EXEC sp_executesql @CMD
GO
This process will reate a field 'MyField' from table 'MyTable' and depending on its content will assign value of either 1 or 2 to a declared variable @RET. It is this variable that I somehow must pass back to the calling procedure 'MyProcedure'.
Whilst it is possible, as was suggested i this thread, to create a temporary table where the value would be written and subsequently read by the calling procedure, I am trying to avoid this method (on account of processing speed as well as because the calling procedure may be executed simultaneously by a number of users, so I would need to somehow separate each instance when returning the code).
Is there any way to achieve this without physically writing to a database?
-
How about writing a stored procedure that returns the @RET value as an output parameter.
-
-
The use of OPENQUERY is an interesting suggestion, but unfortunately this command does not accept variables as its arguments. The batch of commands I need to execute is stored in a variable, which must be passed to the executing command.
Any thoughts? Anyone?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|