Dynamic SQL

[bobby1977]

Hi!

To obtain a value using dynamic SQL, in Oracle I used
EXECUTE IMMEDIATE 'SELECT name FROM emp WHERE empno = :no' INTO lcv_name USING lnv_empno
where:
lnv_empno variable with employee ID value
lcv_name variable filled after statement execution with employee name

In SQL Server i can execute almost any kind of strings, but I do not know how to obtain a value in an output variable

how can I invoke sp_executeSQL with string
'SELECT @lcv_name = name FROM emp WHERE empno = @lnv_empno'
?
I mean how can i declare param definition string to work with an input and an output param ?

Thank you !

 

[mfairchild]

Assuming you what a single value in a variable and not in a resultset...

<b>Create a procedure:</b>

Create Proc TestProcedure @EmployeeID int
@OutputValue varchar(30) OUTPUT
AS

SELECT @OutputValue = Name
FROM emp
WHERE empno = @EmployeeID

<b>Call the procedure:</b>

DECLARE @EmployeeName varchar(30)

EXEC TestProcedure 1234, @EmployeeName OUTPUT

 

[mfairchild]

heh...HTML tags don't work here....