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

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure

Author

Topic

ddombadoh
Starting Member

7 Posts

Posted - 2011-09-14 : 11:07:17

Hi All

I am using SQL Server 2008, and is stuck with a stored procedure issue.
I have a table with the following structure:

ID Data
1 1;2;3
2 1;3
3 1;4

I want a stored procedure that will take a parameter value (e.g 1) and then will return all rows which has data containing 1.

Any help on this will be appreciated.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 11:11:19

[code]CREATE PROC GetData
@Val int
AS
SELECT *
FROM Table
WHERE ';' + Data + ';' LIKE '%;' + CAST(@val AS varchar(10)) + ';%'
[/code]

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-14 : 11:11:33

you can use the like operator

select <stuff>
from yourTable
where date like '1;%'

Jim

Everyday I learn something that somebody else already knew

ddombadoh
Starting Member

7 Posts

Posted - 2011-09-14 : 11:30:45

Thank you visakh16

It worked like magic.
However I was wondering if it's possible to pass the parameter as a string also (e.g 1;3) and then return non duplicate rows that contain the values 1 or 3.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 11:49:18

will the data be containing values in ascending order only?

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

ddombadoh
Starting Member

7 Posts

Posted - 2011-09-14 : 11:57:41

No. It could be mixed.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 13:16:14

then you want it to return the records where all data have come but in any order?

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

ddombadoh
Starting Member

7 Posts

Posted - 2011-09-14 : 13:41:19

Exactly so visakh16.

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-09-14 : 14:31:54

nobody has asked WHY you have semicolon separated data?
Why are you not using notepad to store this instead of a relational database?

Getting the results you want may be nice, but there are MASSIVE problems with the overall design, and fixing that would be best.

(Trying to fill in for JC here

)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-14 : 21:39:58

quote:
Originally posted by DonAtWork

nobody has asked WHY you have semicolon separated data?
Why are you not using notepad to store this instead of a relational database?

Getting the results you want may be nice, but there are MASSIVE problems with the overall design, and fixing that would be best.

Agreed... But, sadly, sometimes we do not have such luxuries. If the OP is in such a case, (s)he could use a 'tally' table here.

Here is an example solution using a 'numbers' table containing the col 'n' as the value. OP can search here for articles on how tally tables work.

The sample data is stored in #t (in this example).


--parse your input variable = @val and store in a table var = @param

DECLARE @param TABLE (ct INT IDENTITY(1,1), data VARCHAR(10))

INSERT @param
SELECT DISTINCT NullIf(SubString(';' + @val + ';' , n.n , CharIndex(';' , ';' + @val + ';' , n.n) - n.n) , '') AS data  
FROM numbers n
WHERE LEN(@val) <= Len(';' + @val + ';') AND SubString(';' + @val + ';' , n.n - 1, 1) = ';' 
AND CharIndex(';' , ';' + @val + ';' , n.n) - n.n > 0

--retrieve the id from your table which contains the matching data values found in the input value
	
SELECT r.id
FROM
(
	SELECT d.*
	FROM @param v
	LEFT JOIN
	(
		SELECT id,RTRIM(LTRIM(NullIf(SubString(';' + t.data + ';' , n.n , CharIndex(';' , ';' + t.data + ';' , n.n) - n.n) , ''))) AS data  
		FROM #t t, numbers n
		WHERE ID <= Len(';' + t.data + ';') AND SubString(';' + t.data + ';' , n.n - 1, 1) = ';' 
		AND CharIndex(';' , ';' + t.data + ';' , n.n) - n.n > 0
	) d ON d.data = v.data
) r
GROUP BY r.id 
HAVING COUNT(r.id) = (SELECT COUNT(*) FROM @param)

Hope that helps OP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources