Solved

Passing a sub-query as a parameter to a stored procedure

Posted on 2014-02-01
11
3,008 Views
Last Modified: 2014-06-06
Hi There - My experience with SQL is pretty limited but there is a stored procedure I need to use, the original query is below:
****************************************
USE [mydatabase]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[stored_procedure_name]
            @RO_set_no = NULL,
            @user_id = NULL

SELECT      'Return Value' = @return_value

GO
*****************************************

What I want to do, is pass a sub-select query to the "@RO_set_no" parameter. How would I go about doing this?
0
Comment
Question by:rwaterz
[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
  • 6
  • 5
11 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39826946
this is how I would do it, by employing the dynamic sql into your stored procedure.

USE [mydatabase]
GO

DECLARE      @return_value int
DECALRE      @Ro_set_no  VARCHAR(1000)
SET @Ro_set_no = 'SELECT A FROM Table1'


EXEC      @return_value = [dbo].[stored_procedure_name]
            @RO_set_no = @Ro_set_no,
            @user_id = NULL

SELECT      'Return Value' = @return_value
GO

Open in new window


Within the stored procedure change the code as

CREATE PROCEDURE stored_procedure_name
(
@ro_set_no VARCHAR(max),user_id varchar(100)
)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX)
....
....

SET @SQL = 'SELECT * FROM TABLE2 T2'
SET @SQL = @SQL + ' JOIN (' + @ro_set_no + ') T1 on t1.pid = t2.fid'

exec sp_executeSQL @SQL

END

Open in new window

0
 

Author Comment

by:rwaterz
ID: 39830514
Hi Surendra - Thanks for the response. I am testing this out now, but what do I fill in for the

"@SQL"

from this block:


CREATE PROCEDURE stored_procedure_name
(
@ro_set_no VARCHAR(max),user_id varchar(100)
)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX)
....
....

SET @SQL = 'SELECT * FROM TABLE2 T2'
SET @SQL = @SQL + ' JOIN (' + @ro_set_no + ') T1 on t1.pid = t2.fid'

exec sp_executeSQL @SQL

END

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39830547
Ok, let me explain

Let us say you have query (with the sub query) as below

Query1
select * 
from Table2 T2,
(
select * FROM table1 
) T1
WHERE T1.pid = t2.fid

Open in new window


in the above case the select * from table1 will be the subquery and the select * from table2 will be called as outerquery

Inorder to pass the subquery as a parameter and execute it within a stored procedure, you need use the dynamic SQL

So, the above statement can be converted as

Query 2
DECLARE @SQL NVARCHAR(4000)
DECLARE @SubQuery NVARCHAR(2000)
SET @SubQuery = 'SELECT * FROM Table1'
SET @Var = 'select * 
from Table2 T2,
(' + 
@SubQuery
+ ' 
) T1
WHERE T1.pid = t2.fid'
PRINT @SQL
EXEC(@SQL)

Open in new window


The result set of both query 1 and query 2 will be same... but the query 2 use dynamic SQL and the sub query can be passed through a variable here...

The variable can be a parameter for the stored procedure as well....



Now you see the @SQL should be filled with the outer query in your SQL Join where you are trying to use the sub query
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:rwaterz
ID: 39830691
So the sub query statement I want to use would NOT go into this query?:

USE [mydatabase]
GO

DECLARE      @return_value int
DECALRE      @Ro_set_no  VARCHAR(1000)
SET @Ro_set_no = 'SELECT A FROM Table1'


EXEC      @return_value = [dbo].[stored_procedure_name]
            @RO_set_no = @Ro_set_no,
            @user_id = NULL

SELECT      'Return Value' = @return_value
GO

Open in new window


It sounds like only the column of the table should go there.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39830728
the @ro_set_no will hold your complete sub query without parenthesis ofcourse.
0
 

Author Comment

by:rwaterz
ID: 39830790
Thanks for explaining, but this might be outside of my understanding. Here is the stored procedure:

USE [impresario]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[TP_RO_SET_MAIN]
		@RO_set_no = NULL,
		@user_id = NULL

SELECT	'Return Value' = @return_value

GO

Open in new window


USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[TP_RO_SET_MAIN]    Script Date: 02/03/2014 13:04:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object:  Stored Procedure dbo.TP_RO_Set_Main    Script Date: 9/21/97 3:50:20 PM ******/
/****** Object:  Stored Procedure dbo.TP_RO_Set_Main    Script Date: 9/16/97 12:15:25 PM ******/
ALTER       PROCEDURE [dbo].[TP_RO_SET_MAIN]
(
@RO_set_no int,
@user_id char(8)
)
 
WITH RECOMPILE
 
AS
--
/*****************************************************************************************************
Author Ajay Wadhia
 
Runs all the Rollovers for a RO_Set_no in one go
 
Called from : Gooesoft Report Generator

Modified 8/2/2000 by CWR -- added source_no and add_to_order_dt to list of items retrieved
from t_ro_set_master and passed on to other procedures.  Also fixed batch_type which had been
commented out and set to 22 

Modified 6/14/2001 by CWR -- added ability to regenerate dynamic list

Modified 8/29/2001 by CWR -- added @user_id parm which comes from the front end so that batches
get the right owner (instead of 'Admin' all the time)

Modified 8/30/2001 by CWR -- now passing batch_notes to batch creation routine

Modified 1/25/2002 by CWR -- order_dt value can now be null

*******************************************************************************************************/
 
 
-- close any transaction if already exists
IF @@trancount > 0
BEGIN
	COMMIT
END
 
 
declare @create_temp_ind char
declare @batch_type int
declare @batch_no int
declare @source_product_list varchar(255)
declare @target_pkg_no int
declare @target_perf_no int
declare @list_no int
declare @appeal_no int
declare @MOS smallint
declare @price_type int
declare @order_dt datetime
declare @hold_until_dt datetime
declare @source_no int
declare @add_to_order_dt datetime
declare @batch_notes varchar(55)

set nocount on

Select @batch_notes = 'Created by Rollover Set ' + convert(varchar, @ro_set_no)

SELECT @create_temp_ind = 'N'
 
--- pick the parameters in variables from to_set_master table
 
select 	@list_no = list_no, 
	@appeal_no = appeal_no, 
	@MOS = MOS,
 	@price_type = Price_type, 
	@order_dt = order_dt,
	@hold_until_dt = hold_until_dt,
	@batch_type = batch_type,
	@source_no = source_no,
	@add_to_order_dt = add_to_order_dt
from 	T_RO_Set_Master
where 	RO_Set_no = @RO_Set_no
 
	----Check parameters if any is missing abort
 
 
----@appeal_no IS NULL
	IF 	@MOS IS NULL
		OR	@price_type IS NULL
--		OR	@order_dt IS NULL	-- order_dt can now be null for rollover (CWR 1/25/2002)
	BEGIN
		Raiserror('Sorry Parameters misssing Cannot Continue',16,2)
		RETURN 	(-999)
	END
 
IF IsNull(@list_no, 0) > 0
  Begin
	-- regenerate list  CWR 6/14/2001
	IF Exists (Select 1 From t_list Where list_no = @list_no 
		and Ltrim(convert(varchar, criteria)) Like 'Select%')
		and (Select recalc_status From t_list Where list_no = @list_no) = 'D'

			Exec RP_Generate_list  @list_no = @list_no,  @display_names = 'N'
  End 
 
DECLARE @batch_stage varchar(40)
Declare Sets cursor for	/*should this not say for update of ..vivek ?????*/
 
select source_product_list, target_pkg_no, target_perf_no, batch_no, batch_stage
	from T_RO_set_detail
	where RO_set_no = @RO_set_No
 
declare @transaction_ind char
select @transaction_ind = 'N'
 
open Sets
fetch sets into @source_product_list, @target_pkg_no, @target_perf_no, @batch_no, @batch_stage
while @@fetch_status = 0
Begin
	----batch rules to be added here whenever finalised 09/09/98
	---	print "starting rolllover "
	---	Begin transaction here
	select @transaction_ind = 'N'
	---SELECT @batch_no batch_no
	IF @batch_stage = 'FINISHED'
	---@batch_no IS NOT NULL
	BEGIN
		---Print 'finding next ro'
		FETCH sets INTO @source_product_list, @target_pkg_no, @target_perf_no, @batch_no, @batch_stage
 		CONTINUE
	END
	/*
	IF @@trancount = 0
	BEGIN
		BEGIN TRANSACTION rollover
		SELECT @transaction_ind = 'Y'
	END
	*/
	IF @batch_no IS NULL
	BEGIN
		----Create batch_no and open batch for each rollover in a set
		EXEC	TP_RO_create_uncntl_batch 
			@batch_type, 
			@batch_no = @batch_no out, 
			@user_id = @user_id,
			@batch_notes = @batch_notes
		
		update T_RO_Set_detail set batch_no = @batch_no where current of sets
	END

	EXEC TP_Rollover_main @batch_no, @source_product_list, @target_pkg_no,
	@target_perf_no ,@list_no, @appeal_no, @MOS, @price_type, @order_dt,
	@hold_until_dt, @create_temp_ind, @source_no, @add_to_order_dt

	--- I must know if i shud close the batch
	Exec TP_RO_close_uncntl_batch @batch_no
	SELECT @batch_no = null
	/* making batch null (If batch_no is not null)  */
----	end Transaction here
---	print "ending ro after updating TX_RO_set_detail"
	/*
	If @transaction_ind = 'Y'
	BEGIN
		if @@error = 0
		BEGIN
			COMMIT TRAN rollover
		END
		ELSE
		BEGIN
			ROLLBACK
			----TRAN rollover
		END
	END
	*/
	SELECT @transaction_ind = 'N'
	fetch sets into @source_product_list, @target_pkg_no, @target_perf_no, @batch_no, @batch_stage
end
 
close Sets
Deallocate Sets
--set nocount off
 
exec TP_RO_Set_Report @RO_Set_no
---output @RO_Set_no
return

Open in new window


And here is the sub query I want to use:

Select
	a.ro_set_no,
From
	T_RO_SET_MASTER a Join
	T_PKG p on p.season = 55 and ('xp' /*CHANGE ME*/ + SUBSTRING (a.description,1,1) + Substring(a.description,2,3)  +'-R'+ Substring(a.description,5,1)) = p.pkg_code Join
	T_PKG pp on pp.season = 56 and a.description = pp.description
Where
	a.target_season = 56
	and p.facility_no = 82

Open in new window


I might be able to make better sense of it if I can see where you would make the edits.
0
 

Author Comment

by:rwaterz
ID: 39830791

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[TP_RO_SET_MAIN]    Script Date: 02/03/2014 13:04:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object:  Stored Procedure dbo.TP_RO_Set_Main    Script Date: 9/21/97 3:50:20 PM ******/
/****** Object:  Stored Procedure dbo.TP_RO_Set_Main    Script Date: 9/16/97 12:15:25 PM ******/
ALTER       PROCEDURE [dbo].[TP_RO_SET_MAIN]
(
@RO_set_no int,
@user_id char(8)
)
 
WITH RECOMPILE
 
AS
--
/*****************************************************************************************************
Author Ajay Wadhia
 
Runs all the Rollovers for a RO_Set_no in one go
 
Called from : Gooesoft Report Generator

Modified 8/2/2000 by CWR -- added source_no and add_to_order_dt to list of items retrieved
from t_ro_set_master and passed on to other procedures.  Also fixed batch_type which had been
commented out and set to 22 

Modified 6/14/2001 by CWR -- added ability to regenerate dynamic list

Modified 8/29/2001 by CWR -- added @user_id parm which comes from the front end so that batches
get the right owner (instead of 'Admin' all the time)

Modified 8/30/2001 by CWR -- now passing batch_notes to batch creation routine

Modified 1/25/2002 by CWR -- order_dt value can now be null

*******************************************************************************************************/
 
 
-- close any transaction if already exists
IF @@trancount > 0
BEGIN
	COMMIT
END
 
 
declare @create_temp_ind char
declare @batch_type int
declare @batch_no int
declare @source_product_list varchar(255)
declare @target_pkg_no int
declare @target_perf_no int
declare @list_no int
declare @appeal_no int
declare @MOS smallint
declare @price_type int
declare @order_dt datetime
declare @hold_until_dt datetime
declare @source_no int
declare @add_to_order_dt datetime
declare @batch_notes varchar(55)

set nocount on

Select @batch_notes = 'Created by Rollover Set ' + convert(varchar, @ro_set_no)

SELECT @create_temp_ind = 'N'
 
--- pick the parameters in variables from to_set_master table
 
select 	@list_no = list_no, 
	@appeal_no = appeal_no, 
	@MOS = MOS,
 	@price_type = Price_type, 
	@order_dt = order_dt,
	@hold_until_dt = hold_until_dt,
	@batch_type = batch_type,
	@source_no = source_no,
	@add_to_order_dt = add_to_order_dt
from 	T_RO_Set_Master
where 	RO_Set_no = @RO_Set_no
 
	----Check parameters if any is missing abort
 
 
----@appeal_no IS NULL
	IF 	@MOS IS NULL
		OR	@price_type IS NULL
--		OR	@order_dt IS NULL	-- order_dt can now be null for rollover (CWR 1/25/2002)
	BEGIN
		Raiserror('Sorry Parameters misssing Cannot Continue',16,2)
		RETURN 	(-999)
	END
 
IF IsNull(@list_no, 0) > 0
  Begin
	-- regenerate list  CWR 6/14/2001
	IF Exists (Select 1 From t_list Where list_no = @list_no 
		and Ltrim(convert(varchar, criteria)) Like 'Select%')
		and (Select recalc_status From t_list Where list_no = @list_no) = 'D'

			Exec RP_Generate_list  @list_no = @list_no,  @display_names = 'N'
  End 
 
DECLARE @batch_stage varchar(40)
Declare Sets cursor for	/*should this not say for update of ..vivek ?????*/
 
select source_product_list, target_pkg_no, target_perf_no, batch_no, batch_stage
	from T_RO_set_detail
	where RO_set_no = @RO_set_No
 
declare @transaction_ind char
select @transaction_ind = 'N'
 
open Sets
fetch sets into @source_product_list, @target_pkg_no, @target_perf_no, @batch_no, @batch_stage
while @@fetch_status = 0
Begin
	----batch rules to be added here whenever finalised 09/09/98
	---	print "starting rolllover "
	---	Begin transaction here
	select @transaction_ind = 'N'
	---SELECT @batch_no batch_no
	IF @batch_stage = 'FINISHED'
	---@batch_no IS NOT NULL
	BEGIN
		---Print 'finding next ro'
		FETCH sets INTO @source_product_list, @target_pkg_no, @target_perf_no, @batch_no, @batch_stage
 		CONTINUE
	END
	/*
	IF @@trancount = 0
	BEGIN
		BEGIN TRANSACTION rollover
		SELECT @transaction_ind = 'Y'
	END
	*/
	IF @batch_no IS NULL
	BEGIN
		----Create batch_no and open batch for each rollover in a set
		EXEC	TP_RO_create_uncntl_batch 
			@batch_type, 
			@batch_no = @batch_no out, 
			@user_id = @user_id,
			@batch_notes = @batch_notes
		
		update T_RO_Set_detail set batch_no = @batch_no where current of sets
	END

	EXEC TP_Rollover_main @batch_no, @source_product_list, @target_pkg_no,
	@target_perf_no ,@list_no, @appeal_no, @MOS, @price_type, @order_dt,
	@hold_until_dt, @create_temp_ind, @source_no, @add_to_order_dt

	--- I must know if i shud close the batch
	Exec TP_RO_close_uncntl_batch @batch_no
	SELECT @batch_no = null
	/* making batch null (If batch_no is not null)  */
----	end Transaction here
---	print "ending ro after updating TX_RO_set_detail"
	/*
	If @transaction_ind = 'Y'
	BEGIN
		if @@error = 0
		BEGIN
			COMMIT TRAN rollover
		END
		ELSE
		BEGIN
			ROLLBACK
			----TRAN rollover
		END
	END
	*/
	SELECT @transaction_ind = 'N'
	fetch sets into @source_product_list, @target_pkg_no, @target_perf_no, @batch_no, @batch_stage
end
 
close Sets
Deallocate Sets
--set nocount off
 
exec TP_RO_Set_Report @RO_Set_no
---output @RO_Set_no
return

Open in new window

Select
	a.ro_set_no,
From
	T_RO_SET_MASTER a Join
	T_PKG p on p.season = 55 and ('xp' /*CHANGE ME*/ + SUBSTRING (a.description,1,1) + Substring(a.description,2,3)  +'-R'+ Substring(a.description,5,1)) = p.pkg_code Join
	T_PKG pp on pp.season = 56 and a.description = pp.description
Where
	a.target_season = 56
	and p.facility_no = 82

Open in new window

USE [impresario]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[TP_RO_SET_MAIN]
		@RO_set_no = NULL,
		@user_id = NULL

SELECT	'Return Value' = @return_value

GO

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39830813
I think what you are looking for is some thing like this

USE [impresario]
GO

DECLARE	@return_value int
DECLARE @I INT

SELECT @I = (
Select
	a.ro_set_no,
From
	T_RO_SET_MASTER a Join
	T_PKG p on p.season = 55 and ('xp' /*CHANGE ME*/ + SUBSTRING (a.description,1,1) + Substring(a.description,2,3)  +'-R'+ Substring(a.description,5,1)) = p.pkg_code Join
	T_PKG pp on pp.season = 56 and a.description = pp.description
Where
	a.target_season = 56
	and p.facility_no = 82
)

EXEC	@return_value = [dbo].[TP_RO_SET_MAIN]
		@RO_set_no = @I,
		@user_id = NULL

SELECT	'Return Value' = @return_value

GO

Open in new window



No need to change the stored proc...
0
 

Author Comment

by:rwaterz
ID: 39830822
I see. I was confused by your OP and thought the stored procedure needed changing as well.

I ran the above, (with the exception of the added"," after "a.ro_set_no" and received this message:

"Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 50000, Level 16, State 2, Procedure TP_RO_SET_MAIN, Line 88
Sorry Parameters misssing Cannot Continue

(1 row(s) affected)"
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39830990
change a bit, added top 1 clause to get around that error, check this out and let me know if it solves your issue

USE [impresario]
GO

DECLARE	@return_value int
DECLARE @I INT

SELECT @I = (
Select
	top 1 a.ro_set_no
From
	T_RO_SET_MASTER a Join
	T_PKG p on p.season = 55 and ('xp' /*CHANGE ME*/ + SUBSTRING (a.description,1,1) + Substring(a.description,2,3)  +'-R'+ Substring(a.description,5,1)) = p.pkg_code Join
	T_PKG pp on pp.season = 56 and a.description = pp.description
Where
	a.target_season = 56
	and p.facility_no = 82
)

EXEC	@return_value = [dbo].[TP_RO_SET_MAIN]
		@RO_set_no = @I,
		@user_id = NULL

SELECT	'Return Value' = @return_value

GO

Open in new window

0
 

Author Comment

by:rwaterz
ID: 39831103
We're getting close. That worked, however, I received 47 results and should have received 213.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

626 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