Solved

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

Posted on 2014-02-01
11
2,255 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
  • 6
  • 5
11 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rwaterz
Comment Utility
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
Comment Utility
the @ro_set_no will hold your complete sub query without parenthesis ofcourse.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:rwaterz
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
We're getting close. That worked, however, I received 47 results and should have received 213.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

7 Experts available now in Live!

Get 1:1 Help Now