CODEWITHSUNDEEP
sql-serverstored-procedures
Indra Ramasani
Indra Ramasani

Reputation: 105

Stored procedure parameter contains multiple values

My stored procedure parameter contains more than 2 values (eg: create stored procedure recentAssetList @recentAssetList = (id1,id2,..)) then with these parameter how can I get data from a table?

Upvotes: 2

Views: 1692

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21766

You can use splittion function like that and pass the values in comcatenated string:

CREATE function [dbo].[csv2tbl](@list nvarchar(max), @delimiter nvarchar(10))
returns @res table ([index] int PRIMARY KEY, col nvarchar(max))
AS BEGIN
with tbl_for_csv as
(
select 0 as [index] ,left(@list + @delimiter+@delimiter,charindex(@delimiter,@list + @delimiter+@delimiter) -1)as col,
right(@list + @delimiter+@delimiter,len(@list + @delimiter+@delimiter) - charindex(@delimiter,@list + @delimiter+@delimiter)) as Str
union all
select [index]+1, left(Str,charindex(@delimiter,Str) - 1)as Col,
right(Str,len(Str) - charindex(@delimiter,Str)) from tbl_for_csv
where len(right(Str,len(Str) - charindex(@delimiter,Str))) > 0
)
INSERT @res
select [index], col from tbl_for_csv option (MAXRECURSION 0);
return;
END

In sql2008+ you can pass values to SP thru user defined table types variable (see @Shark answer)

Upvotes: 0

user596075
user596075

Reputation:

SQL Server doesn't support that logic. Here are some options:

1. Split them amongst many parameters

create procedure yourProc
    @FirstParam varchar(10),
    @SecondParam varchar(10)
as
    -- etc.
go

If some of these parameters may be null you can do this:

create procedure yourProc
    @FirstParam varchar(10) = null,
    @SecondParam varchar(10) = null
as
    select *
    from yourTable
    where
        ((@FirstParam is null) or (SomeCol1 = @FirstParam)) and
        ((@SecondParam is null) or (SomeCol2 = @SecondParam))
go

2. Pass a read only table

create type yourTableData 
as table
(
    id int not null
)
go

create procedure yourProc
    @yourInput yourTableData readonly
as

    select *
    from yourTable
    where id in
    (
        select id
        from @yourInput
    )
go

Upvotes: 1

Related Questions