SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 t-sql 208 r2 call stored procedure & output
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scamper
Yak Posting Veteran

52 Posts

Posted - 04/17/2014 :  11:41:55  Show Profile  Reply with Quote
I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'

EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules


The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data
all the rows from the stored procedure multiple times. However can you tell me the following:
1. How can I have the stored procedure return distinct rows?
2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".
when I change the sql above to:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)

EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName

That is when I get the error message.

A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure
that I believe alot of t-sqls and stored procedures will use.

Thus can you show me sql that will solve this issue?

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/17/2014 :  11:47:49  Show Profile  Reply with Quote
you can only provide parameters that the SP expects (has as input parameters).
You have three options:
- modify that procedure to do what you want it to do.
- use that procedure as a model for your own new procedure that does what you want it to do.
- create a table or temp table that matches the structure of the out put of that procedure. INSERT yourTable EXEC that procedure. Then select what you want out of your table.

If you want help with options one or two then you'll need to post the source code of the procedure and explain the changes you need.


Be One with the Optimizer
TG
Go to Top of Page

scamper
Yak Posting Veteran

52 Posts

Posted - 04/17/2014 :  12:39:33  Show Profile  Reply with Quote
You mentioned the following as an option: use that procedure as a model for your own new procedure that does what you want it to do.

If I did that, I would need to modify the stored procedure to get my columns as 'output' values, correct? If so, can you show me sql where the results are returned as output parameters?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/17/2014 :  13:40:03  Show Profile  Reply with Quote
Keep in mind that "results" from a stored procedure is often tabular in nature (multiple rows and columns) where as an output variable is scalar meaning a single value.

>>can you show me sql where the results are returned as output parameters?
sure:

go
create proc myProc @myOutputVar int OUTPUT as set @myOutputVar = 10
go

declare @result int
exec myProc @myOutputVar = @result OUTPUT
select @result as [OutputResult]

go
drop proc myproc
go

OUTPUT:
OutputResult
------------
10


Be One with the Optimizer
TG
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 04/17/2014 :  14:04:28  Show Profile  Reply with Quote
Return the results of the stored proc into a temp table, then SELECT against that, specifying whatever additional criteria you want:


--change this table create to exactly match the output of the proc
CREATE TABLE #sproom_results (
    customerid int,
    custname varchar(50),
    --...
    )

DECLARE @return_value int,
 @endYear SMALLINT = 2014,
 @CustomerID INT = '9999', 
	@Schedules CHAR(1) = N'C'

INSERT INTO #sproom_results ( ... )
EXEC [dbo].[sproom] @endYear
	,@CustomerID
	,@Schedules

SELECT DISTINCT *
FROM #sproom_results
WHERE
    custname = @CustName

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000