June 13, 2013 at 5:21 am
hi All,
I have a stored procedure where in I pass two paramaters StartDate and EndDate and return the start of the week
-------Query--
---DECLARE @Sample TABLE (Week Date, TotalSUm int)
---Declare @Testvariable Date
---INSERT INTO @Sample
---EXEC dbo.sp_testsp '2013-05-02','2013-05-04'
---set @Testvariable = (select distinct week from @Sample)-- this returns start of the week i.e
--2013-04-29
---select @Testvariable --returns the start of week--
And then I pass on the @Testvariable in the some other select statement.
The above query returs fine, but if the EXEC dbo.sp_testsp is being passed with multiple values, then the error
--Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.--
I understand Set variable can accpet only a Scalar variable. if in case I pass paramaters to procudre as below
EXEC dbo.sp_testsp '2013-05-02','2013-05-07',it should return
2013-04-29
2013-05-06
and I wanted to pass the above to select statement and retirve the results for corresponding weeks.
ANy help on this? thanks
June 13, 2013 at 6:20 am
If you want to have a resultset (multiple valies) into a single variable, you need to declare is as a table variable.
It could also be possible to create a (global temporary-)table to hold the resultset.
June 13, 2013 at 6:24 am
You can also create a function which accepts a single parameter and returns the start of the week for that parameter. You can call this function using CROSS APPLY with your initial resultset @Sample.
June 13, 2013 at 6:25 am
Post the procedure. It probably just needs a small tweak and not any temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply