Multivalue parameter to a stored procedure

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

Multivalue parameter to a stored procedure

Author

Topic

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-25 : 03:43:40

Hi,
I need to write a stored procedure that takes a multivalue parameter "Restaurants". How can i decalre this parameter and what's the syntax to write in the query to get the records having one of the passed Restaurants as restaurant.
I tried the "IN" statement but it didn't work or maybe i used it wrong.
Can anybody tell me how please?
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 04:12:07

[code]CREATE PROCEDURE Yourprocedure
...,
@Restaurants varchar(8000)
AS
SELECT...
FROM table1
JOIN table2....
JOIN dbo.ParseValues(@Restaurants) r
ON r.Val=t1.RestaurantField
WHERE...
GO[/code]

ParseValues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-25 : 04:54:38

Hi, thanks for you reply.
What i need exactly is using my parameter in the "Where" clause, not in the "Join". So i need to get only the records that have one of the Restaurants passed as parameter as restaurant.
How is it feasible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 04:59:03

quote:
Originally posted by 1sabine8

Hi, thanks for you reply.
What i need exactly is using my parameter in the "Where" clause, not in the "Join". So i need to get only the records that have one of the Restaurants passed as parameter as restaurant.
How is it feasible?

they are one and same. You can do it via join as given earlier or use it in WHERE with IN

CREATE PROCEDURE Yourprocedure
...,
@Restaurants varchar(8000)
AS
SELECT...
FROM table1
JOIN table2....
....
WHERE  t1.RestaurantField IN (SELECT Val FROM dbo.ParseValues(@Restaurants))
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-25 : 06:03:49

Also refer http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 04:58:25

Hi,
I'm trying to write this statement. Can anyone help me with the quotes? (it's all in red and giving syntax error.

DECLARE @SQL varchar(1000)

SET @SQL =
'INSERT INTO #AllLoyalCustomers(ContactId)
SELECT distinct dbo.FilteredContact.contactid
FROM dbo.FilteredContact INNER JOIN
dbo.FilteredNew_dineintakeawaycommentcard ON dbo.FilteredContact.contactid = dbo.FilteredNew_dineintakeawaycommentcard.new_contactid
WHERE (dbo.FilteredContact.new_loyalcustomer = '+True' AND (dbo.FilteredNew_dineintakeawaycommentcard.new_date BETWEEN @FromDate AND @ToDate) AND dbo.FilteredNew_dineintakeawaycommentcard.new_restaurantname IN ('+ @RestaurantName')'

The where clause is giving a problem.
How can i solve it?

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 05:28:17

i think i fixed the statement but i got an error saying:
Msg 103, Level 15, State 4, Procedure AllLoyalCustomers, Line 49
The identifier that starts with 'INSERT INTO #AllLoyalCustomers(ContactId)
SELECT distinct dbo.FilteredContact.contactid
FROM dbo.FilteredContact INNER JOIN db' is too long. Maximum length is 128.

How can i solve it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 05:28:45

[code]SET @SQL =
'INSERT INTO #AllLoyalCustomers(ContactId)
SELECT distinct dbo.FilteredContact.contactid
FROM dbo.FilteredContact INNER JOIN
dbo.FilteredNew_dineintakeawaycommentcard ON dbo.FilteredContact.contactid = dbo.FilteredNew_dineintakeawaycommentcard.new_contactid
WHERE (dbo.FilteredContact.new_loyalcustomer = ''True'' AND (dbo.FilteredNew_dineintakeawaycommentcard.new_date BETWEEN '+@FromDate+' AND '+@ToDate+' ) AND dbo.FilteredNew_dineintakeawaycommentcard.new_restaurantname IN ('+ @RestaurantName')'[/code]

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 05:55:32

Hi,
This didn't work.
I got the following error:
Msg 102, Level 15, State 1, Procedure AllLoyalCustomers, Line 54
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 06:04:13

quote:
Originally posted by 1sabine8

Hi,
This didn't work.
I got the following error:
Msg 102, Level 15, State 1, Procedure AllLoyalCustomers, Line 54
Incorrect syntax near ')'.

there were couple of un wanted braces. modify like below and try

SET @SQL = 
'INSERT INTO #AllLoyalCustomers(ContactId)
SELECT distinct dbo.FilteredContact.contactid
FROM dbo.FilteredContact INNER JOIN
dbo.FilteredNew_dineintakeawaycommentcard 
ON dbo.FilteredContact.contactid = dbo.FilteredNew_dineintakeawaycommentcard.new_contactid
WHERE dbo.FilteredContact.new_loyalcustomer = ''True'' AND dbo.FilteredNew_dineintakeawaycommentcard.new_date BETWEEN '+@FromDate+' AND '+@ToDate+'  AND dbo.FilteredNew_dineintakeawaycommentcard.new_restaurantname IN ('+ @RestaurantName+ ')'

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 06:10:23

Hi thanks again for your reply.
This seemed to work but when i executed it i got the following error:
Msg 241, Level 16, State 1, Procedure AllLoyalCustomers, Line 49
Conversion failed when converting datetime from character string.
How can i solve it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 06:15:12

Why dynamic sql at all? Use stored procedures with parameters

INSERT		#AllLoyalCustomers
		(
			ContactId
		)
SELECT DISTINCT	dbo.FilteredContact.contactid
FROM		dbo.FilteredContact AS fc
INNER JOIN	dbo.FilteredNew_dineintakeawaycommentcard AS d ON d.new_contactid = fc.contactid
INNER JOIN	dbo.ParseValues(@Restaurants) AS r ON r.Val = d.new_restaurantname 
WHERE		fc.new_loyalcustomer = 1
		AND d.new_date BETWEEN @FromDate AND @ToDate

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 06:17:14

Don't use dynamic sql! You will have to put single quotes around every restaurant in the variable to make this work

SET @SQL = 
'INSERT INTO #AllLoyalCustomers(ContactId)
SELECT distinct dbo.FilteredContact.contactid
FROM dbo.FilteredContact INNER JOIN
dbo.FilteredNew_dineintakeawaycommentcard 
ON dbo.FilteredContact.contactid = dbo.FilteredNew_dineintakeawaycommentcard.new_contactid
WHERE dbo.FilteredContact.new_loyalcustomer = ''True'' AND dbo.FilteredNew_dineintakeawaycommentcard.new_date BETWEEN ''' + cast(@FromDate as varchar(30)) + ''' AND ''' + cast(@ToDate as varchar(30)) + '''  AND dbo.FilteredNew_dineintakeawaycommentcard.new_restaurantname IN ('+ @RestaurantName+ ')''

E 12°55'05.63"
N 56°04'39.26"

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 06:26:12

So which statement i need to use? Your first reply or your second one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 06:33:33

quote:
Originally posted by 1sabine8

So which statement i need to use? Your first reply or your second one?

first one if you dont want dynamic sql or even this

INSERT		#AllLoyalCustomers
		(
			ContactId
		)
SELECT DISTINCT	dbo.FilteredContact.contactid
FROM		dbo.FilteredContact AS fc
INNER JOIN	dbo.FilteredNew_dineintakeawaycommentcard AS d ON d.new_contactid = fc.contactid
WHERE		fc.new_loyalcustomer = 1
		AND d.new_date BETWEEN @FromDate AND @ToDate
AND ',' + @RestaurantName+',' LIKE '%,'+d.new_restaurantname+',%'

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 06:40:20

I tried the one with dbo.ParseValues, got the following error once i executed it:
Invalid object name 'dbo.ParseValues'.
So what's exactly 'dbo.ParseValues'?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 06:46:58

you can find it here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 07:06:20

Oh god! This is unbelievable!
I created the function and the used this code:
INSERT #AllLoyalCustomers
(
ContactId
)
SELECT DISTINCT dbo.FilteredContact.contactid
FROM dbo.FilteredContact AS fc
INNER JOIN dbo.FilteredNew_dineintakeawaycommentcard AS d ON d.new_contactid = fc.contactid
INNER JOIN dbo.ParseValues(@Restaurants) AS r ON r.Val = d.new_restaurantname
WHERE fc.new_loyalcustomer = 1
AND d.new_date BETWEEN @FromDate AND @ToDate

I'm now getting this error:
The multi-part identifier "dbo.FilteredContact.contactid" could not be bound.
Any idea guys?
Thanks for your support!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 07:13:15

quote:
Originally posted by 1sabine8

Oh god! This is unbelievable!
I created the function and the used this code:
INSERT #AllLoyalCustomers
(
ContactId
)
SELECT DISTINCT ~~dbo.FilteredContact~~fc.contactid
FROM dbo.FilteredContact AS fc
INNER JOIN dbo.FilteredNew_dineintakeawaycommentcard AS d ON d.new_contactid = fc.contactid
INNER JOIN dbo.ParseValues(@Restaurants) AS r ON r.Val = d.new_restaurantname
WHERE fc.new_loyalcustomer = 1
AND d.new_date BETWEEN @FromDate AND @ToDate

I'm now getting this error:
The multi-part identifier "dbo.FilteredContact.contactid" could not be bound.
Any idea guys?
Thanks for your support!

modify like above

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-09-29 : 07:25:05

Really really thanks.
This worked but i got a new error on execution :(
It's:
Msg 245, Level 16, State 1, Procedure AllLoyalCustomers, Line 49
Conversion failed when converting the varchar value 'ABC' to data type int.
What does it need?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 07:29:58

What datatype is contactid from filteredcontact table?
What datatype is contactid from #allloyalcustomers table?

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources