t-sql 208 r2 call stored procedure & output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql 208 r2 call stored procedure & output

Author

Topic

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-04-17 : 11:41:55

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-17 : 11:47:49

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

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-04-17 : 12:39:33

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?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-17 : 13:40:03

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-17 : 14:04:28

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources