April 17, 2014 at 9:44 am
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?
April 17, 2014 at 10:06 am
Create a temp table and pipe the results of the procedure to the table.
Then you can query as you prefer:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'
CREATE TABLE #someTable (
someColumn varchar(100),
someOtherColumn varchar(100)
)
INSERT #someTable
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
SELECT DISTINCT someColumn
FROM #someTable
-- Gianluca Sartori
April 17, 2014 at 10:06 am
wendy elizabeth (4/17/2014)
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?
You can't tell a stored procedure not to return some of the columns. It will ALWAYS return ALL of the columns.
Not really sure anybody can help you solve the "issue" because from what you posted we don't know what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 10:19 am
you might have to change it to a table valued function may be and see if it can serve you the purpose. see this article http://www.sommarskog.se/share_data.html
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply