October 12, 2009 at 5:35 am
hi every one
how to pass Passing multi-value parameter in stored procedure? i have tried with the below,
create procedure usp_test
(@MonthNo varchar(200))
as
-- Here i am passing a list of values like 01,02,03,05,10,08 etc
begin
select col1,col2,col3 from emp where (DATEPART(MM,reportingPeriod) IN(@MonthNo);
end
i am getting this error
Msg 245, Level 16, State 1, Procedure Usp_GetQuarterlyTxSubdetails, Line 16
Conversion failed when converting the varchar value '02,04' to data type int.
i again modified above
create procedure usp_test
(@MonthName varchar(200))
as
-- Here i am passing a list of values like january,february... etc
begin
select col1,col2,col3 from emp where (DATENAME(MM,reportingPeriod) IN(@MonthName);
end
Msg 8144, Level 16, State 2, Procedure usp_GetMonthlyTxSubdetails, Line 0
Procedure or function usp_GetMonthlyTxSubdetails has too many arguments specified.
actually the first one is suitable to my requirement.
can any one have idea on this one
Thanks
Rock...
October 12, 2009 at 5:38 am
Try this link for solutions
October 12, 2009 at 7:34 am
here reportingperiod is a datatime datatype
i am getting month no from it. i executed the function.
CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
then i created the sp using the function.
create procedure usp_test
(@MonthNo varchar(200))
as
-- Here i am passing a list of values like 01,02,03,05,10,08 etc
begin
select col1,col2,col3 from emp as a join iter$simple_intlist_to_tbl (@MonthNo) as b on (a.datepart(mm,reportingPeriod)=b.@number)
where (DATEPART(MM,reportingPeriod) IN(@MonthNo);
end
executed successful
after that i execute the sp then
Msg 4121, Level 16, State 1, Procedure test, Line 07
Cannot find either column "a" or the user-defined function or aggregate "a.DATEPART", or the name is ambiguous.
October 13, 2009 at 1:44 am
I Got it
Thanks alot
Regards
Rock..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply