Link to home
Create AccountLog in
Avatar of crundle
crundle

asked on

Pass Query results to stored procedure

Have a query that provides one row with 3 columns of data. This data then need to be passed as 3 separate values to a stored procedure.

The stored procedure requires Value1, value2, value3. (example "EXECUTE crosstab2 EMP_View, EMP_Event, EMP_Order") How do break the query results into 3 separate variables?

DECLARE @selectfield varchar(8000),@ClientID int, @ListType varchar(100), @Queryval varchar(100)
set @ClientID = 2 
set @ListType  = 'air'
 
set @Queryval =  '('+replace( replace( 
                  (SELECT CASE 
                              WHEN Upper(@ListType) = Upper('CONTAINER') Then '(ShipmentView) as EMP_View,(EventView) as EMP_Event,(field_order)'
                              WHEN Upper(@ListType) = Upper('TRUCK') THEN  '(Roadview) as EMP_View,(RoadEvent) as EMP_Event,(RoadField_order)'
                              WHEN Upper(@ListType) = Upper('Air') THEN 'AirView[AirEvent]null'
                              ELSE  'null  as EMP_View,null,null'
                        End)
,']',') as EMP_Event,(') ,'[',') as EMP_View,(') + ') as EMP_order'
 
EXEC ('SELECT  '+ @Queryval + ' FROM customer_employees where EmployeeID =' + @ClientID)
 
 
 
Results from above code
                  EMP_Event                                              |                  EMP_View                      |   EMP_order
----------------------------------------------------------------------|------------------------------------------------|---------------
*CLIENT_NAME*,*PONumber*,*stAddress2*,*rtAddress2*,*SSLType_of_Move*  |  *27*,*28*,*29*,*30*,*31*,*32*,*33*,*34*,*35*  |     NULL

Open in new window

Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Think different. You have two options:

1)  Since you are already using dynamic SQL, you can simply carry that out to its logical conclusion (see below) and execute the target stored procedure inside the dynamic SQL

2) If you need the three values, then look at returning values from sp_executeSQL.  Use the top half of the query below, returning the emp_event, emp_view, and emp_order variables, and using them in the outer procedure as needed.
DECLARE @selectfield varchar(8000),
	@ClientID int, 
	@ListType varchar(100), 
	@Queryval varchar(1000)
 
set @ClientID = 2 
set @ListType  = 'air'
set @Queryval =  'DECLARE @emp_event varchar(100), @emp_view varchar(100), @emp_order varchar(100)
SELECT @emp_event=('+replace( replace( 
                  (SELECT CASE 
                              WHEN Upper(@ListType) = Upper('CONTAINER') Then '(ShipmentView) as EMP_View,(EventView) as EMP_Event,(field_order)'
                              WHEN Upper(@ListType) = Upper('TRUCK') THEN  '(Roadview) as EMP_View,(RoadEvent) as EMP_Event,(RoadField_order)'
                              WHEN Upper(@ListType) = Upper('Air') THEN 'AirView[AirEvent]null'
                              ELSE  'null  as EMP_View,null,null'
                        End)
,']','), 
    @emp_view=(') ,'[','), 
    @emp_order=(') + ')
FROM customer_employees
WHERE employee_id = ' + CAST(@clientID AS VARCHAR(8)) + '
 
EXEC MyStoredProc @emp_event, @emp_view, @emp_order'

Open in new window

Avatar of crundle
crundle

ASKER

I'm missing something, I copied your solution and it shows 'Command(s) completed successfully.' but not getting reults.

For simplicity I have removed all the replace.. and statically placed the column names.
DECLARE @selectfield varchar(8000),
	@ClientID int, 
	@ListType varchar(100), 
	@Queryval varchar(1000)
 
set @ClientID = 2 
set @ListType  = 'air'
 
set @Queryval =  'DECLARE @emp_view varchar(100), @emp_event varchar(100), @emp_order varchar(100)
SELECT @emp_event=ShipmentView, 
	   @emp_view=EventView,  
		@emp_order=field_order
FROM customer_employees
WHERE employee_id = 2
 
EXEC crosstab2 @emp_view,@emp_event'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account