array as parameter - 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

array as parameter

Author

Topic

EZEK
Starting Member

8 Posts

Posted - 2008-01-28 : 11:39:38

hi guys im having some problems with arrays as parameter
what i want to do is pass a array of comma separated values i.e 12,24,36 and use the in(@parameter) but it doesnt work

i've found some solutions, like
ALTER PROCEDURE [dbo].[GetLawyers] ( @LawyerTypeIDs VARCHAR(100)
AS

DECLARE @SQL VARCHAR(2000)

SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE [PARCELAS] IN (' + @LawyerTypeIDs + ')'
EXECUTE (@SQL)

but in my case doesnt work.

here is a part of my code
SELECT * FROM (
SELECT *, ROW_NUMBER()
OVER (PARTITION BY cod_banco ORDER BY coeficiente asc) AS RecID FROM tb_valor_bancos
where
(1 = CASE WHEN @TABELA IS NULL THEN 1
ELSE CASE WHEN TABELA = @TABELA THEN 1
ELSE 0
END
END
)
AND (1 = CASE WHEN @PARCELAS IS NULL THEN 1
ELSE CASE WHEN PARCELAS in(@PARCELAS) THEN 1
ELSE 0
END
END
)
...

i know there is a way to pass the array into a table but it doesnt work too

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:50:32

http://www.sommarskog.se/arrays-in-sql.html

E 12°55'05.25"
N 56°04'39.16"

EZEK
Starting Member

8 Posts

Posted - 2008-01-28 : 12:04:01

i use SQL SERVER 2005 and i think it does not accept
CREATE FUNCTION fixstring_single(@str nvarchar(MAX), @itemlen tinyint)
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,<--error
str = substring(@str, @itemlen * (n.Number - 1) + 1, @itemlen)
FROM Numbers n
WHERE n.Number <= len(@str) / @itemlen +
CASE len(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END)
error

Msg 208, Level 16, State 1, Procedure fixstring_single, Line 3
Invalid object name 'Numbers'.

EZEK
Starting Member

8 Posts

Posted - 2008-01-28 : 13:59:37

thx i used the function with inner join and worked

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 04:06:45

quote:
Originally posted by EZEK

i use SQL SERVER 2005 and i think it does not accept
CREATE FUNCTION fixstring_single(@str nvarchar(MAX), @itemlen tinyint)
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,<--error
str = substring(@str, @itemlen * (n.Number - 1) + 1, @itemlen)
FROM Numbers n
WHERE n.Number <= len(@str) / @itemlen +
CASE len(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END)
error

Msg 208, Level 16, State 1, Procedure fixstring_single, Line 3
Invalid object name 'Numbers'.

You should have a Number table

or replace FROM Numbers n

with

FROM (select row_number() over (order by s1.name) as number from sysobjects s1 cross join sysobjects s2) as n

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources