?
Solved

SQL How to alias column output calling sp_executesql from stored proc

Posted on 2013-05-17
8
Medium Priority
?
2,845 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

616 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