help with 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)

help with stored procedure

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-04 : 16:28:11

Hello, Is there a way to pass multiple parameters into a stored procedure such as an array? I have a project at work where I need to do this so I picked the northwind database as an example. When the procedure below is executed it will return O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME when the user inputs a company name EXEC GET_COMPANYNAME 'CompanyA'

What if the user had a list of 10,15 or a 1000 Company Names they wanted to input, how would this work? I tried EXEC GET_COMPANYNAME 'CompanyA', 'CompanyB', but i got errors when doing this.

CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(60) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE COMPANYNAME = @CNAME AND ORDERID = @OID

EXEC GET_COMPANYNAME 'CompanyA'

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-04 : 16:29:08

i had a minor typo in the first thread

Hello, Is there a way to pass multiple parameters into a stored procedure such as an array? I have a project at work where I need to do this so I picked the northwind database as an example. When the procedure below is executed it will return O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME when the user inputs a company name EXEC GET_COMPANYNAME 'CompanyA'

What if the user had a list of 10,15 or a 1000 Company Names they wanted to input, how would this work? I tried EXEC GET_COMPANYNAME 'CompanyA', 'CompanyB', but i got errors when doing this.

CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(60) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE COMPANYNAME = @CNAME

EXEC GET_COMPANYNAME 'CompanyA'

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-04 : 16:29:57

Read up on table-type parameters.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-04 : 18:00:47

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

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-04 : 19:23:11

Thanks, this seems like a lot of work, i'll think to think of another way around this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 21:50:20

make it like


CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(max) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE  ',' + @CNAME + ',' LIKE '%,' + COMPANYNAME + ',%'

And call it like

EXEC GET_COMPANYNAME 'CompanyA,CompanyB,CompanyC'

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-05 : 05:01:05

quote:

CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(max) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE ',' + @CNAME + ',' LIKE '%,' + COMPANYNAME + ',%'

And call it like

EXEC GET_COMPANYNAME 'CompanyA,CompanyB,CompanyC'

Well that would *work* but you'd be unable to use any INDEX on the column in question.

quote:
Thanks, this seems like a lot of work, i'll think to think of another way around this.

Well if you can't be arsed to do it right then yeah -- go hack up something and then live with the performance and code maintenance problems that'll cause.

What you SHOULD do is:

1) Declare a user defined table type to model the list of entities. Something like


CREATE TYPE tvp_CompanyNames AS TABLE (
    [CompanyName] VARCHAR(20) PRIMARY KEY CLUSTERED
    )
GRANT EXECUTE ON TYPE::tvp_CompanyNames TO <the restricted application role / user>

2) Amend the sp to take a new parameter of this table type
3) Then change the SQL to JOIN to that table parameter.
4) Build the table type in your calling code and pass to sp

5) Relax.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-05 : 16:10:53

interesting, thanks everyone, i'll give this a shot

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-05 : 16:18:48

thanks, when i run that, i get the following error

Msg 8144, Level 16, State 2, Procedure GET_COMPANYNAME, Line 0
Procedure or function GET_COMPANYNAME has too many arguments specified.

CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(max) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE ',' + @CNAME + ',' LIKE '%,' + COMPANYNAME + ',%'

And call it like

EXEC GET_COMPANYNAME 'CompanyA,CompanyB,CompanyC'

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 02:24:40

quote:
Originally posted by joe8079

thanks, when i run that, i get the following error

Msg 8144, Level 16, State 2, Procedure GET_COMPANYNAME, Line 0
Procedure or function GET_COMPANYNAME has too many arguments specified.

CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(max) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE ',' + @CNAME + ',' LIKE '%,' + COMPANYNAME + ',%'

GO
--And call it like

EXEC GET_COMPANYNAME @CNAME = 'CompanyA,CompanyB,CompanyC'

how did you call this??

Karthik
http://karthik4identity.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 04:31:54

quote:
Originally posted by joe8079

thanks, when i run that, i get the following error

Msg 8144, Level 16, State 2, Procedure GET_COMPANYNAME, Line 0
Procedure or function GET_COMPANYNAME has too many arguments specified.

CREATE PROCEDURE GET_COMPANYNAME
@CNAME NVARCHAR(max) AS

SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.ADDRESS, C.COMPANYNAME
FROM ORDERS O JOIN CUSTOMERS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE ',' + @CNAME + ',' LIKE '%,' + COMPANYNAME + ',%'

And call it like

EXEC GET_COMPANYNAME 'CompanyA,CompanyB,CompanyC'

nope if you call like this it wont cause any errors.
please post exact statement you're using

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

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-06 : 20:58:04

i'm sorry, you are correct, I was calling it wrong.
I was puting quotes around 'companya', 'companyb' etc..
i will try again later.
thank you for your help on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 22:42:25

ok...try and let us know outcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources