Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

output parameters in sp_executesql?

Posted on 2002-03-14
5
Medium Priority
?
776 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:juarrero
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 6864937
for output parameters take this example that counts the number of rows:

USE Northwind
DECLARE @SQLString NVARCHAR(500)
DECLARE @Parames NVARCHAR(100)
DELCARE @Row_Count INT

'This is setting up the query you want to execute
SET @SQLString = N'SELECT @res = COUNT(*) FROM Employees WHERE LastName LIKE ''D%'''

'Ah yes, the @res parameter is INT, and you want to get that back afterwards
SET @Params = N'@res INT OUTPUT'

'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


so if you want to supply fill 2 params:

GO

USE Northwind
DECLARE @SQLString NVARCHAR(500)
DECLARE @Parames NVARCHAR(100)
DELCARE @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%'


CHeers
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6864941
sorry for the typos:

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
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6864949
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



0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 6864980
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..
0
 

Author Comment

by:juarrero
ID: 6889181
Hello,

I think angellll's first comment is good enough.

Thanks to everybody for your help.

Ignacio
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question