how to in stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to in stored procedure

Author

Topic

mmcfar
Starting Member

8 Posts

Posted - 2010-03-27 : 21:54:15

Hello All,
I have the following select and XQuery in a stored procedure hardcoded with the variables 10,11 I would like to pass these to the procedure.

WORKS
SELECT * FROM Users WHERE (SiteList.exist('/Configuration/SitesUsed/SiteID[text() = (10,11)]') = 1)

DOESN'T WORK
@sitelist varchar(100)

SELECT * FROM Users WHERE (SiteList.exist('/Configuration/SitesUsed/SiteID[text() = (@sitelist)]') = 1)

exec procdurename '10' <--returns nothing

In C# in the middle tier I passed a string like this:
string uSiteList = "(10,11)"
string sql = string.Format("SELECT * FROM Users WHERE (SiteList.exist('/Configuration/SitesUsed/SiteID[text() = ({0})]') = 1)", SiteList);

My question is: what type of variable and how do I pass it
varchar, nchar
i.e "10,11" or '10,11'

---current procedure---
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.GetAllActiveUsers (@sitelist varchar(100))
AS
--doesn't work
--SELECT * FROM Users WHERE (SiteList.exist('/Configuration/SitesUsed/SiteID[text()= @sitelist]') = 1) AND (Permission >= 0) Order By Lastname
--works with one variable '10' not '10,11'
--SELECT * FROM Users WHERE (SiteList.exist('/Configuration/SitesUsed/SiteID[text()= xs:string(sql:variable("@sitelist"))]') = 1) AND (Permission >= 0) Order By Lastname
--hardcoded
SELECT * FROM Users WHERE (SiteList.exist('/Configuration/SitesUsed/SiteID[text() = (10,11)]') = 1) AND (Permission >= 0) Order By Lastname
GO
-----------------
Thanks,
any help greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 02:52:59

are you trying to return those nodes which is having one of passed values in variable as a value in SiteID node?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mmcfar
Starting Member

8 Posts

Posted - 2010-03-28 : 10:47:16

quote:
Originally posted by visakh16

are you trying to return those nodes which is having one of passed values in variable as a value in SiteID node?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes. I am trying to return all rows in the table Users that match the SiteID node of the XML data type SiteList which match either 10 or 11. (in this example it is 10 or 11 it could be a list of 1 to 50)

I think the problem is how I am passing the variable to the procedure or how I am handling it once it is passed. It works when hardcoded, it seems it should work if using a variable.

Thanks for the quick response

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 13:22:08

why dont you use a string splitting function like below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

and then use it in your query like


SELECT u.* FROM Users u
CROSS JOIN dbo.ParseValues(@Str,',')f
WHERE SiteList.exist('/Configuration/SitesUsed/SiteID[text()= xs:string(sql:column("f.Val"))]') = 1) 
AND (u.Permission >= 0) 
Order By u.Lastname

declare @Str and Pass @Str as '10,11,12...'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mmcfar
Starting Member

8 Posts

Posted - 2010-03-28 : 14:32:30

Ahhh master. You are awesome! This works really well, thank you very much for the function.

My next question really shows my newbieness. How do I select DISTINCT. My PK is UserID. Thank you very much for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 00:27:17

What according to you represents a distinct set? I mean based on what group you want to take distinct? would be better if you could show it with help of some sample data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources