March 25, 2013 at 6:05 am
Dear all,
I have two tables namely:
1. User table
2. Location table
Given below are their design details:
User table columns:
UserID INT, (Primary Key)
UserForename VARCHAR(200),
UserSurname VARCHAR(200),
LocationID INt (foriegn key)
Location table columns:
LocationID INT, (Primary Key)
LocationName VARCHAR(200)
Issue:
I need a store procedure to retrieve user details based on the LocationIDs passed as the input parameter where the input parameter is the array of LocationIDs separated by comma. [for eg: '1,2,3']
Please kindly help me with a stored procedure as explained above.
Thanks in advance,
Ram
March 25, 2013 at 6:24 am
One method would be a string splitter.
Take a look in my signature for Jeff's string splitter and call it in your query
declare @temp table(Userid int, forename varchar(200), surname varchar(200), locationid int)
insert into @temp values (1,'ant','green',1),(2,'green','ant',2)
declare @param varchar(10) = '1,2'
SELECT
UserID,
Forename,
Surname
FROM
@temp t
INNER JOIN
dbo.DelimitedSplit8K(@param,',') f
on
t.locationid = f.Item
March 25, 2013 at 6:29 am
CREATE Proc usersbyLocation(@locids varchar(max)=NULL)
AS
BEGIN
Declare @STR varchar(max)
SET @STR='SELECTUserID,
UserForename,
UserSurname,
LocationName
fromtUser JOIN
tLocation on tUser.locationID=tLocation.LocationID
Where tLocation.LocationID in ('+@locids+')'
PRINT @locids
Exec (@str)
END
--Test
--Exec usersbyLocation '1,2,3,4,5,6'
March 25, 2013 at 8:48 am
Pulivarthi Sasidhar (3/25/2013)
CREATE Proc usersbyLocation(@locids varchar(max)=NULL)
AS
BEGIN
Declare @STR varchar(max)
SET @STR='SELECTUserID,
UserForename,
UserSurname,
LocationName
fromtUser JOIN
tLocation on tUser.locationID=tLocation.LocationID
Where tLocation.LocationID in ('+@locids+')'
PRINT @locids
Exec (@str)
END
--Test
--Exec usersbyLocation '1,2,3,4,5,6'
Look above, no dynamic sql required.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply