SQL How to alias column output calling sp_executesql from stored proc

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???
I_sAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
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
 
Surendra NathTechnology LeadCommented:
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
 
I_sAuthor Commented:
@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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Surendra NathTechnology LeadCommented:
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
 
I_sAuthor Commented:
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
 
Surendra NathTechnology LeadCommented:
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
 
I_sAuthor Commented:
Here is the result:

Query execution with debug statements
0
 
I_sAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.