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

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.

you can use openquery. check this link

http://forums.databasejournal.com/sh...ight=openquery

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?