asked on

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

Surendra Nath

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

I_s

ASKER

@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!

Surendra Nath

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.

I_s

ASKER

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?

Surendra Nath

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

I_s

ASKER

Here is the result:

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

I_s

ASKER

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!