Passing input parm problem - 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 2000 Forums

SQL Server Development (2000)

Passing input parm problem

Author

Topic

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 09:59:48

I am new to SQL and I am having a problem sending an input parm to a query I made. The query accepts an input parm formatted in VB.NET.

The parm is a string of 1 to many user ids. The parm works fine if only 1 user id is passed, but returns nothing when multiple values are passed.

Something seems to be happening during the variable/value transformation in SQL (I think)

Here is my value passed: 1134,1136

here is my query:

CREATE PROCEDURE dbo.web_users_RPT

@user_id_str varchar(1000) -- select records equal to or between a range of user ids

AS

BEGIN

SELECT

company_name =
case
when csd.sub_div_name is not null then c.comp_name + ' - ' + csd.sub_div_name
when cd.div_name is not null then c.comp_name + ' - ' + cd.div_name
when c.comp_name is not null then c.comp_name
end,

contact_name = LTRIM(con.contact_givname + ' ' + con.contact_surname),
contact_givname = ISNULL(con.contact_givname,''),
contact_surname = ISNULL(con.contact_surname,''),

office_phone =
case
when csd.sub_div_name is not null then LTRIM(ISNULL(csd.phn_no,''))
when cd.div_name is not null then LTRIM(ISNULL(cd.phn_no,''))
when c.comp_name is not null then LTRIM(ISNULL(c.phn_no,''))
end,

direct_phone =
case
when con.phn_ext is not null then ISNULL(con.phn_no,'') + ' Ext: ' + con.phn_ext
when con.phn_ext is null then ISNULL(con.phn_no,'')
end,

w.date_entered,

address1 =
case
when csd.sub_div_name is not null then isnull(csd.address,'')
when cd.div_name is not null then isnull(cd.address,'')
when c.comp_name is not null then isnull(c.address,'')
end,

address2 =
case
when csd.sub_div_name is not null then isnull(csd.address_2,'')
when cd.div_name is not null then isnull(cd.address_2,'')
when c.comp_name is not null then isnull(c.address_2,'')
end,

city =
case
when csd.sub_div_name is not null then isnull(csd.city,'')
when cd.div_name is not null then isnull(cd.city,'')
when c.comp_name is not null then isnull(c.city,'')
end,

prov =
case
when csd.sub_div_name is not null then isnull(csd.prov,'')
when cd.div_name is not null then isnull(cd.prov,'')
when c.comp_name is not null then isnull(c.prov,'')
end,

post_cde =
case
when csd.sub_div_name is not null then isnull(csd.post_cde,'')
when cd.div_name is not null then isnull(cd.post_cde,'')
when c.comp_name is not null then isnull(c.post_cde,'')
end,

email = LTRIM(ISNULL(con.email_address,'')),

uid = w.user_id,
cname = c.comp_name,
dname = ISNULL(cd.div_name,''),
sname = ISNULL(csd.sub_div_name,''),
w.user_name,
w.password,
w.comments,
w.lic_no,
w.cd_only,
w.aba_cd_no,
w.exp_cd_no,
w.aba_update_cd,
w.exp_update_cd,
w.sub_date,
w.exp_date,
w.subscription,
cd_check = 0,
blank_ind = 0

from ((((web_users w left join company c on w.comp_id = c.comp_id)
left join contacts con on w.contact_id = con.contact_id)
left join company_div cd on w.comp_div_id = cd.comp_div_id)
left join company_sub_div csd on w.comp_sub_div_id = csd.comp_sub_div_id)

WHERE w.user_id IN ( @user_id_str )

order by c.comp_name

END
GO

It is the WHERE statement that is failing me.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 10:05:22

that's a job for dynamic sql:
http://www.sqlteam.com/item.asp?ItemID=4619

Go with the flow & have fun! Else fight the flow

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-15 : 10:07:32

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30588&SearchTerms=CHARINDEX

This will help you...

EDIT: No need for dynamic sql...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 10:09:54

true. i'll have to remember the charindex method

Go with the flow & have fun! Else fight the flow

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 10:53:56

hmmm....the dynamic SQL I understand and I have used. What that tells me is that there IS some wierd variable/value transformation taking place which is why once EVERYTHING is put into a string the passed parm works.

The charindex thing....way over my head but it would appear that for me to use it I would have to basically make a database call for every value in my string....not very efficient...but I am a noob ;)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 10:58:06

WHERE w.user_id IN ( @user_id_str ) doesn't work because the sql translates this into:
WHERE w.user_id IN ('1,2,3,4,5,6,7')
the correct way is
WHERE w.user_id IN ('1','2','3','4','5','6','7')
using
set @SQL = @SQL + ' WHERE w.user_id IN (' + @user_id_str + ')'
translates in
'WHERE w.user_id IN (1,2,3,4,5,6,7)'

does that make sense to you?

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 11:03:07

charindex method is not complex, basically it's just a function which takes the @user_id_str as parameter, splits it and inserts the data into a table. then you can join that table to the original table. that way you don't need dynamic sql.

Go with the flow & have fun! Else fight the flow

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 11:05:23

...set @SQL = @SQL + ' WHERE w.user_id IN (' + @user_id_str + ')'
...

Yes I understand this and it works. I was trying to avoid the dynamic sql.

I have even formatted the value in VB.NET to be simular to your example: '1','2','3' but it also fails. I assume it is the comma.

just read your statement about charindex....ooooh *light goes on* I think I understand it now......

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 11:19:18

or maybe not

After I create a simular function in "user defined Functions" how do I call it in my sql statement?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 11:27:54

insetad of
WHERE t1.col1 in (2,4,5,6,7)
you do:

t1 inner join dbo.yourUdfName('2,4,5,6,7') t2 on t1.col1 = t2.numberval

Go with the flow & have fun! Else fight the flow

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 12:17:07

Woohoo!

Thank-you Thank-you Thank-you

That works like a charm. I did however have to add an IF statement when I only passed 1 value.

My function reads:

CREATE FUNCTION udf_CSVTable( @Str varchar(8000) )
RETURNS @t TABLE (p_userid int)
AS
BEGIN
DECLARE @x int, @c varchar(200)
SELECT @x = charindex(',',@Str,1)

IF @x = 0
set @c = @Str
ELSE
WHILE @x <> 0
BEGIN
SELECT @c = SUBSTRING(@Str,1,@x-1)
INSERT INTO @t
VALUES (@c)

SELECT @Str = SUBSTRING(@Str,@x+1,LEN(@Str)-LEN(SUBSTRING(@Str,1,@x)))
SELECT @x = charindex(',',@Str,1)
END

INSERT INTO @t
VALUES (@Str)

RETURN
END

Is that correct or is there a better way to catch only 1 passed value?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 12:42:26

try this:


Declare @x int
Set @x = 1
While (Charindex (',', @Str)>0)
Begin
	Insert Into @t (p_userid)
	Select 	p_userid = ltrim(rtrim(Substring( @Str,1,Charindex (',', @Str)-1)))
	Set @Str = Substring( @Str,Charindex (',' ,@Str)+1,len( @Str))
	Set @x = @x + 1
End
	
Insert Into @t (p_userid)
Select p_userid = ltrim(rtrim(@Str))

Go with the flow & have fun! Else fight the flow

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 13:07:42

It works! Thanks so much.

2 quick questions...

1. what is @x doing ? I see it set to 1 and then incremented...but I do not see it used.

2. The first time in the loop p_userid is null but since it is declared as a varchar did it insert a blank record into the table?

I just removed @x and everything seems to work.

Also I ran a test and saw that no blank record was inserted.

thanks again!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 13:45:58

1. forget the @x. my mistake.
2. when is p_userid null??

Go with the flow & have fun! Else fight the flow

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-09-15 : 15:00:37

I see you have it solved, but another option is to pass two separate parameters to the SPROC, don't need dynamic sql. I'm assuming you're looking for userids BETWEEN two values, as you stated up top.

where w.userid BETWEEN @UserIDRange1 AND @UserIDRange2

Chinwa
Starting Member

20 Posts

Posted - 2004-09-15 : 15:55:30

my mistake

I misread the loop - p_userid is not null

The code works great! *does the snoopy dance*

Thanks again!

Steelkilt, no, I am looking for values in a group of values I pass to the stored procedure. SPROC? does that stand for stored procedure?
I R NOOB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources