Using sp_executesql 1

[ahartong]

I am trying to write a SQL statement in a stored procedure, and I'm not sure if SQL has the functionality to handle what I am tring to do.

Once the call is made, I use the @PONum variable in the proc.

Here is my example statement:
SET @SQLString = N'select ' + @PONUM + ' = PONUMBER from ' + @InterID + '.dbo.POP40100
EXEC sp_executesql @SQLString

I am passing the DB name(@InterID) into the proc, and I need to return the PONumber(@PONum).

Is it possible to return a value into the parameter @PONum when using sp_executesql? It seems to handle in params ok, but not out. What can I do?

 

[tlbroadbent]

The proper syntax for sp_executesql is

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,
@parm2OUT=@parmRET2 OUTPUT

See the following article for more info.

INF: Using Output Parameters with sp_executesql

http://support.microsoft.com/support/kb/articles/Q262/4/99.ASP

Your example:

SET @SQLString =
N'Select @PONUM = PONUMBER From ' +
@InterID + '.dbo.POP40100'

EXEC sp_executesql
@SQLString,
N'@PONum Int Output',
@PONum = @PONum Output Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.