asked on

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

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?

Surendra Nath

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

rwaterz

ASKER

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

Surendra Nath

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

rwaterz

ASKER

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.

Surendra Nath

the @ro_set_no will hold your complete sub query without parenthesis ofcourse.

rwaterz

ASKER

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.

rwaterz

ASKER

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

Surendra Nath

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

rwaterz

ASKER

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)"

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

rwaterz

ASKER

We're getting close. That worked, however, I received 47 results and should have received 213.