Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL How to alias column output calling sp_executesql from stored proc

Posted on 2013-05-17
8
Medium Priority
?
2,703 Views
Last Modified: 2013-05-17
Hello experts!

I have created a stored procedure which has one input parameter and three output parameters. From the stored proc I am using dynamic SQL to execute the sp_executesql procedure on a Linked Server.

The query:
ALTER PROCEDURE [dbo].[ValidateSerialExists]
	-- Add the parameters for the stored procedure here
	@i_serlnmbr varchar(50),
	@o_serlnmbr varchar(50) output,
	@o_itemnmbr varchar(50) output,
	@o_locncode varchar(50) output
AS
BEGIN
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	DECLARE @tsql nvarchar(1000)
	DECLARE @paramdec nvarchar(1000)
	SET @tsql = N'SELECT  @o_serlnmbr = [INSTANCE].[dbo].[TABLEONE].COLONE
					 , @o_itemnmbr = [INSTANCE].[dbo].[TABLEONE].COLTWO
					 , @o_locncode = [INSTANCE].[dbo].[TABLEONE].COLTHR
			      FROM    [INSTANCE].[dbo].[TABLEONE] 
			      WHERE ( [INSTANCE].[dbo].[TABLEONE].COLONE= @i_serlnmbr )'
	SET @paramdec = N'@i_serlnmbr varchar(50), @o_serlnmbr varchar(50) output, @o_itemnmbr varchar(50) output, @o_locncode varchar(50) output'
	EXEC [LINKEDSERV\SERVINSTANCE].[master].[dbo].sp_executesql @tsql
							, @paramdec
							, @i_serlnmbr
							, @o_serlnmbr output
							, @o_itemnmbr output
							, @o_locncode output
END
GO

Open in new window


The query returns the results as expected, however, the column names returned are @o_serlnmbr, @o_itemnmbr, and @o_locncode and I would like to alias them as Serial, Item, and Location.

I have tried using the 'as [Serial]' in several different places in the query but SQL complains on execution...

Any suggestions???
0
Comment
Question by:I_s
[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
  • 4
  • 4
8 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39175505
i think what you are looking for is a simple select statment at the end of the batch with aliasing
something as below

ALTER PROCEDURE [dbo].[ValidateSerialExists]
	-- Add the parameters for the stored procedure here
	@i_serlnmbr varchar(50),
	@o_serlnmbr varchar(50) output,
	@o_itemnmbr varchar(50) output,
	@o_locncode varchar(50) output
AS
BEGIN
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	DECLARE @tsql nvarchar(1000)
	DECLARE @paramdec nvarchar(1000)
	SET @tsql = N'SELECT  @o_serlnmbr = [INSTANCE].[dbo].[TABLEONE].COLONE
					 , @o_itemnmbr = [INSTANCE].[dbo].[TABLEONE].COLTWO
					 , @o_locncode = [INSTANCE].[dbo].[TABLEONE].COLTHR
			      FROM    [INSTANCE].[dbo].[TABLEONE] 
			      WHERE ( [INSTANCE].[dbo].[TABLEONE].COLONE= @i_serlnmbr )'
	SET @paramdec = N'@i_serlnmbr varchar(50), @o_serlnmbr varchar(50) output, @o_itemnmbr varchar(50) output, @o_locncode varchar(50) output'
	EXEC [LINKEDSERV\SERVINSTANCE].[master].[dbo].sp_executesql @tsql
							, @paramdec
							, @i_serlnmbr
							, @o_serlnmbr output
							, @o_itemnmbr output
							, @o_locncode output
select @o_serlnmbr Serial, @o_itemnmbr Item, and @o_locncode Location

END
GO

Open in new window

0
 

Author Comment

by:I_s
ID: 39175796
@Neo_jarvis Thanks for the reply. I added the select statement and it works but I now have three result sets.

COLONE  COLTWO  COLTHR
-------------------------------------------
data        data         data

@o_serlnmbr  @o_itemnmbr  @o_locncode
--------------------------------------------------------------------
data                 data                  data

Return Value
--------------------
0

I am trying to consume this in an ASP.Net application using NHibernate and I was hoping to be able to just have the single result set.

COLONE  COLTWO  COLTHR
-------------------------------------------
data        data         data

Return Value
--------------------
0

I am not sure how NHibernate will react to the first result set - but in the mean time i'll test it out!
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39175835
Hi There

DId you try to execute the above stored procedure from SSMS (sql server management studio) and see, what is the result set.
I dont see where the stored procedure is priting the other result, unless you have not sent us the entire code.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:I_s
ID: 39175872
Yup - I am using SSMS and I am right clicking on the stored proc --> "Execute Stored Procedure..." and passing in the input. The following result is what I get:

COLONE  COLTWO  COLTHR
-------------------------------------------
data        data         data

@o_serlnmbr  @o_itemnmbr  @o_locncode
--------------------------------------------------------------------
data                 data                  data

Return Value
--------------------
0

Full code follows:
ALTER PROCEDURE [dbo].[ValidateSerialExists]
	-- Add the parameters for the stored procedure here
	@i_serlnmbr varchar(50),
	@o_serlnmbr varchar(50) output,
	@o_itemnmbr varchar(50) output,
	@o_locncode varchar(50) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	DECLARE @tsql nvarchar(1000)
	DECLARE @paramdec nvarchar(1000)
	SET @tsql = N'SELECT  @o_serlnmbr = [DBASE].[dbo].[DBTABLE].COLONE 
                           , @o_itemnmbr = [DBASE].[dbo].[DBTABLE].COLTWO
                           , @o_locncode = [DBASE].[dbo].[DBTABLE].COLTHR
                        FROM    [DBASE].[dbo].[DBTABLE] 
                        WHERE ( [DBASE].[dbo].[DBTABLE].COLONE = @i_serlnmbr )'
	SET @paramdec = N'@i_serlnmbr varchar(50)
                           , @o_serlnmbr varchar(50) output
                           , @o_itemnmbr varchar(50) output
                           , @o_locncode varchar(50) output'
	EXEC [LINKEDSERVER\INSTANCE].[master].[dbo].sp_executesql 
                                          @tsql
					, @paramdec
					, @i_serlnmbr
					, @o_serlnmbr output
					, @o_itemnmbr output
					, @o_locncode output
	SELECT @o_serlnmbr [Serial], @o_itemnmbr [Item], @o_locncode [Location]
END
GO

Open in new window


My thought was that the second result was coming from the return of executing the @tsql?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39175942
very wierd,

can you give me the output of the below stored proc from your system. I added a few debug statements, in order for me to debug and pinpoint the exact issue.

ALTER PROCEDURE [dbo].[ValidateSerialExists]
	-- Add the parameters for the stored procedure here
	@i_serlnmbr varchar(50),
	@o_serlnmbr varchar(50) output,
	@o_itemnmbr varchar(50) output,
	@o_locncode varchar(50) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	DECLARE @tsql nvarchar(1000)
	DECLARE @paramdec nvarchar(1000)
       select 'after declare'
	SET @tsql = N'SELECT  @o_serlnmbr = [DBASE].[dbo].[DBTABLE].COLONE 
                           , @o_itemnmbr = [DBASE].[dbo].[DBTABLE].COLTWO
                           , @o_locncode = [DBASE].[dbo].[DBTABLE].COLTHR
                        FROM    [DBASE].[dbo].[DBTABLE] 
                        WHERE ( [DBASE].[dbo].[DBTABLE].COLONE = @i_serlnmbr )'
	SET @paramdec = N'@i_serlnmbr varchar(50)
                           , @o_serlnmbr varchar(50) output
                           , @o_itemnmbr varchar(50) output
                           , @o_locncode varchar(50) output'
       select 'Just before linked server execution'
	EXEC [LINKEDSERVER\INSTANCE].[master].[dbo].sp_executesql 
                                          @tsql
					, @paramdec
					, @i_serlnmbr
					, @o_serlnmbr output
					, @o_itemnmbr output
					, @o_locncode output
       select 'after linked server execution'
	SELECT @o_serlnmbr [Serial], @o_itemnmbr [Item], @o_locncode [Location]
       select 'aliased query executed'
END
GO

Open in new window

0
 

Author Comment

by:I_s
ID: 39175981
Here is the result:

Query execution with debug statements
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39176021
ok, I got it now,

As you have defined the three variables o_serlnmbr, o_..... as OUTPUT in the procedure you are getting the last line and this is given in order to show you what they contain as an output.

This should not cause an issue if you are using SQL data source in the .net,
but if it is really causing an issue then i suggest you to remove the variables output tag as shown below.

ALTER PROCEDURE [dbo].[ValidateSerialExists]
	-- Add the parameters for the stored procedure here
	@i_serlnmbr varchar(50),
	@o_serlnmbr varchar(50) ,
	@o_itemnmbr varchar(50),
	@o_locncode varchar(50) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	DECLARE @tsql nvarchar(1000)
	DECLARE @paramdec nvarchar(1000)
	SET @tsql = N'SELECT  @o_serlnmbr = [DBASE].[dbo].[DBTABLE].COLONE 
                           , @o_itemnmbr = [DBASE].[dbo].[DBTABLE].COLTWO
                           , @o_locncode = [DBASE].[dbo].[DBTABLE].COLTHR
                        FROM    [DBASE].[dbo].[DBTABLE] 
                        WHERE ( [DBASE].[dbo].[DBTABLE].COLONE = @i_serlnmbr )'
	SET @paramdec = N'@i_serlnmbr varchar(50)
                           , @o_serlnmbr varchar(50) output
                           , @o_itemnmbr varchar(50) output
                           , @o_locncode varchar(50) output'
	EXEC [LINKEDSERVER\INSTANCE].[master].[dbo].sp_executesql 
                                          @tsql
					, @paramdec
					, @i_serlnmbr
					, @o_serlnmbr output
					, @o_itemnmbr output
					, @o_locncode output
	SELECT @o_serlnmbr [Serial], @o_itemnmbr [Item], @o_locncode [Location]
END
GO

Open in new window


note, before doing that please do test you entire system and see if this stored procedure is used any where else and it didn't break the functionality over there.
0
 

Author Closing Comment

by:I_s
ID: 39176062
Good call on removing the 'output' in the parameter declaration. That gave the result I was looking for. I am working on integrating it into the .net application now and don't anticipate any issues thanks to your help.

Thanks again!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

730 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