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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy