Input Parameter (Bitwise Operation)

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

Input Parameter (Bitwise Operation)

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-09-21 : 14:22:25

Hi Guys,

I am working on Proc which will have multiple input parameters. One of the input parameters can have more than one values seprated by "," or "|". I was looking at bitwise operation and thinking that should handle that multi-valued parameters... anyone who has tackled such issue of handling multi-valued input parameters, please help...

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-21 : 14:56:32

If you're using a delimited list, use Viskah's ParseValues function to turn the list in to a table and then join to it.

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 15:30:18

[code]
IF OBJECT_ID(N'dbo.udf_Table', N'TF') IS NOT NULL
DROP FUNCTION dbo.udf_Table;
GO
CREATE FUNCTION dbo.udf_Table(@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE
(Parameter varchar(255))
AS

BEGIN
SELECT @ParmList = 'a,b,c', @Delim = ','
DECLARE @x int, @Parameter varchar(255)

WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
BEGIN
INSERT INTO @table(Parameter) SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
SELECT @ParmList = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+1, LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
END
INSERT INTO @table(Parameter) SELECT @ParmList
RETURN
END
GO

SELECT * FROM dbo.udf_Table('a,b,c',',')
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-09-21 : 16:12:21

Thanks guys... this will do the trick.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-21 : 16:24:45

quote:
Originally posted by GhantaBro

I am working on Proc which will have multiple input parameters. One of the input parameters can have more than one values separated by "," or "|".<<

This is a horrible way to write SQL or any other language. Did you know that T-SQL can handle up to 2K parameters. Please read my first of two (maybe three) articles on how to do this properly instead of with kludges and homemade parsers:

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-21 : 16:42:43

quote:
Originally posted by jcelko

quote:
Originally posted by GhantaBro

I am working on Proc which will have multiple input parameters. One of the input parameters can have more than one values separated by "," or "|".<<

This is a horrible way to write SQL or any other language. Did you know that T-SQL can handle up to 2K parameters. Please read my first of two (maybe three) articles on how to do this properly instead of with kludges and homemade parsers:

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

--CELKO--
<snip>

Also, in SQL 2008, you can pass tables as paratmers (table-valued parameters). Which is a good way to get structered data into a stored proc. Also, there is the XML datatype.

Also, what does this have to do with bit-wise operations?

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2010-09-22 : 13:58:59

Thanks guys I am on track now with your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources