juarrero
asked on
output parameters in sp_executesql?
Hello,
In the BOL you can find the following piece of code regarding sp_exectesql:
-------------------------- ---------- ---------- ---------- --
USE Northwind
DECLARE @SQLString NVARCHAR(500)
/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)
/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
EXEC sp_executesql @SQLString
GO
-------------------------- ---------- ---------- ---------- --
I have a problem with it. It seems as if parameters in sp_executesql were only input parameters.
How can you get the output of the query?
For example, how to get 'FirstName', or 'LastName'?
Thanks in advance,
juarrero
In the BOL you can find the following piece of code regarding sp_exectesql:
--------------------------
USE Northwind
DECLARE @SQLString NVARCHAR(500)
/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)
/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
EXEC sp_executesql @SQLString
GO
--------------------------
I have a problem with it. It seems as if parameters in sp_executesql were only input parameters.
How can you get the output of the query?
For example, how to get 'FirstName', or 'LastName'?
Thanks in advance,
juarrero
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
see www.nigelrivett.com
sp_executeSQL setting variables from dynamic sql
in this case
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'SELECT @FirstName = FirstName, @LastName = LastName'
declare @FirstName varchar(128) ,
@LastName varchar(128)
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
EXEC sp_executesql @SQLString, N'@LastName varchar(128) output, @FirstName varchar(128) output', @LastName output, @FirstName output
select @LastName, @FirstName
Of course the query must set just one value for the output paramaters.
If you want a list then put the result into a temp table or table variable
insert #a
exec sp_executesql @SQLString
sp_executeSQL setting variables from dynamic sql
in this case
DECLARE @SQLString NVARCHAR(500)
SET @SQLString = N'SELECT @FirstName = FirstName, @LastName = LastName'
declare @FirstName varchar(128) ,
@LastName varchar(128)
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
EXEC sp_executesql @SQLString, N'@LastName varchar(128) output, @FirstName varchar(128) output', @LastName output, @FirstName output
select @LastName, @FirstName
Of course the query must set just one value for the output paramaters.
If you want a list then put the result into a temp table or table variable
insert #a
exec sp_executesql @SQLString
You can do something like this to get other output values:
USE Northwind
DECLARE @SQLString NVARCHAR(500)
DECLARE @firstName VARCHAR(50)
DECLARE @lastName VARCHAR(40)
DECLARE @title VARCHAR(30)
/* Set column list. CHAR(13) is a carriage return.*/
SET @SQLString = N'SELECT @firstName = FirstName, @lastName = LastName, @title = Title' + CHAR(13)
/* Set FROM clause with carriage return. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
EXEC sp_executesql @SQLString, N'@firstName VARCHAR(50) OUTPUT, @lastName VARCHAR(40) OUTPUT, @title VARCHAR(30) OUTPUT', @firstName OUTPUT, @lastName OUTPUT, @title OUTPUT
SELECT @firstName
SELECT @lastName
SELECT @title
GO
Note that the variables used, whether input or output, must be declared in the same order in the second string and in the declarations in the third, etc., string. Meaning that since N'@firstName VARCHAR(50) OUTPUT' is first in the second string, the corresponding variable '@firstName OUTPUT' must be the third parameter; @lastName is second in the string so must be the fourth parameter, etc..
USE Northwind
DECLARE @SQLString NVARCHAR(500)
DECLARE @firstName VARCHAR(50)
DECLARE @lastName VARCHAR(40)
DECLARE @title VARCHAR(30)
/* Set column list. CHAR(13) is a carriage return.*/
SET @SQLString = N'SELECT @firstName = FirstName, @lastName = LastName, @title = Title' + CHAR(13)
/* Set FROM clause with carriage return. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
EXEC sp_executesql @SQLString, N'@firstName VARCHAR(50) OUTPUT, @lastName VARCHAR(40) OUTPUT, @title VARCHAR(30) OUTPUT', @firstName OUTPUT, @lastName OUTPUT, @title OUTPUT
SELECT @firstName
SELECT @lastName
SELECT @title
GO
Note that the variables used, whether input or output, must be declared in the same order in the second string and in the declarations in the third, etc., string. Meaning that since N'@firstName VARCHAR(50) OUTPUT' is first in the second string, the corresponding variable '@firstName OUTPUT' must be the third parameter; @lastName is second in the string so must be the fourth parameter, etc..
ASKER
Hello,
I think angellll's first comment is good enough.
Thanks to everybody for your help.
Ignacio
I think angellll's first comment is good enough.
Thanks to everybody for your help.
Ignacio
USE Northwind
DECLARE @SQLString NVARCHAR(500)
DECLARE @Params NVARCHAR(100)
DECLARE @Row_Count INT
--This is setting up the query you want to execute
SET @SQLString = N'SELECT @res = COUNT(*) FROM Employees WHERE LastName LIKE @Pattern'
--Ah yes, the @res parameter is INT, and you want to get that back afterwards
SET @Params = N'@res INT OUTPUT, @pattern varchar(20)'
-- execute the statement, giving the paramters definition, and adding/supplying the parameters in the
--same order as you did in the @params
EXEC sp_executesql @SQLString, @Params, @Row_Count OUTPUT, 'D%'
SELECT @Row_Count
CHeers