Solved

SQL How to alias column output calling sp_executesql from stored proc

Posted on 2013-05-17
8
2,363 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now