Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-01
11
Medium Priority
?
3,240 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

671 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