multiple parameters in SP.... - 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

multiple parameters in SP....

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-22 : 11:55:02

Hello,

I have this SP (in short):
ALTER PROCEDURE [dbo].[mySP]
-- Add the parameters for the stored procedure here
@ACCOUNTNUM VARCHAR(100),
@DATAAREAID VARCHAR(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select b.Account as Account, b.balance as Balance, isnull(c.balance,0) as [Current], isnull(b1.balance,0) as [30 Day], isnull(b2.balance,0) as [60 Day],
isnull(b3.balance,0) as [90 Day], isnull(b4.balance,0) as [120 Day], CASE b.Stopped_ WHEN '0' then 'No' WHEN '1' THEN 'Invoice' WHEN '2' THEN 'Yes' END as [Stop Status]
from
(
select ctrans.accountnum, ctrans.dataareaid, sum(ctrans.amountcur - ctrans.settleamountcur) as Balance, custtable.name as Account, custtable.blocked as Stopped_
from custtrans ctrans, custtable
where ctrans.dataareaid = @DATAAREAID
and ctrans.accountnum = @ACCOUNTNUM
and ctrans.accountnum = custtable.accountnum
and ctrans.dataareaid = custtable.dataareaid

group by ctrans.accountnum, ctrans.dataareaid, custtable.name, custtable.blocked
) b

Is there someway I can call this SP with multiple @ACCOUNTNUM parameters and have the result set in ONE table???

Something like this: (I know this doesnt work):

exec my_SP @accountnum in ('1','2','3','4')

Thank you in advance.

Regards.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 12:21:57

You can pass them in as a csv list and then parse them out using a user-defined function. Search this site for the csv article as well as the parse functions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-24 : 03:57:21

Also search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:21:03

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

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-29 : 16:47:22

Okay so I have gone down this path:

Create a function that parses thru the commas but I have another question:

How do I add another parmater value to this function?:

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

You normally run this as follows:

EXEC sp_mySP '9, 12, 27, 37'

But I want to run it as follows:

EXEC sp_mySP '9, 12, 27, 37', @parm2 = 'xxx'

How do I edit the function (iter$simple_intlist_to_tbl) so I can still call the sp with multiple paramters???

Giving credit where due: http://www.sommarskog.se/arrays-in-sql-2005.html

Thank you in advance.

Regards.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 16:59:46

What is the purpose of @parm2?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-29 : 17:04:23

quote:
Originally posted by tkizer

What is the purpose of @parm2?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

That parm needs to be passed but not with multiple values, only one...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 17:05:53

But why do you need it in the function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-29 : 17:29:27

quote:
Originally posted by tkizer

But why do you need it in the function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Because I cannot use it as follows:

EXEC sp_mySP '9, 12, 27, 37', @parm2 = 'xxx'

What I am doing is joining the function to the sp (sp_mySP) and then making a call to sp_mySP in another script.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 17:44:34

I'm still not following you. You should not need to modify the function at all. Use @parm2 in the stored procedure, don't add it to the function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-29 : 18:49:26

An alternate way is, in your original stored procedure, replace the section of the where clause that reads:

ctrans.accountnum = @ACCOUNTNUM

with

','+@ACCOUNTNUM+',' like '%,'+ctrans.accountnum+',%'

Then, call the stored procedure like:
exec my_SP @ ACCOUNTNUM = '1,2,3,7'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:19:02

quote:
Originally posted by SQLSoaker

quote:
Originally posted by tkizer

But why do you need it in the function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Because I cannot use it as follows:

EXEC sp_mySP '9, 12, 27, 37', @parm2 = 'xxx'

What I am doing is joining the function to the sp (sp_mySP) and then making a call to sp_mySP in another script.

why do you need @param2 in function? isntit just parsing comma seperated list? whats the purpose of param2 there?

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-30 : 14:02:22

quote:
Originally posted by sunitabeck

An alternate way is, in your original stored procedure, replace the section of the where clause that reads:

ctrans.accountnum = @ACCOUNTNUM

with

','+@ACCOUNTNUM+',' like '%,'+ctrans.accountnum+',%'

Then, call the stored procedure like:
exec my_SP @ ACCOUNTNUM = '1,2,3,7'

Thank you for the reply.

I tried this but it is only picking up the first number in the sequence. In your example @ACCOUNTNUM = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:10:53

nope it should work fine as long as you've only single avlues existing in accountnum field and you pass comma separated values through parameter.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-30 : 14:11:45

This is what I am attempting (with a function)

ALTER PROCEDURE [dbo].[sp_mySP]
-- Add the parameters for the stored procedure here
@ACCOUNTNUM VARCHAR(100),
@DATAAREAID VARCHAR(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select b.Account as Account, b.accountnum_ as [Account Number], b.credit as [Credit Limit], b.balance as Balance, isnull(c.balance,0) as [Current], isnull(b1.balance,0) as [30 Day], isnull(b2.balance,0) as [60 Day],
isnull(b3.balance,0) as [90 Day], isnull(b4.balance,0) as [120 Day], CASE b.Stopped_ WHEN '0' then 'No' WHEN '1' THEN 'Invoice' WHEN '2' THEN 'Yes' END as [Stop Status]
from
(
select ctrans.accountnum, ctrans.dataareaid, sum(ctrans.amountcur - ctrans.settleamountcur) as Balance, custtable.name as Account, custtable.blocked as Stopped_,
custtable.accountnum as accountnum_, custtable.creditmax as credit
from custtrans ctrans, custtable
where ctrans.dataareaid = @DATAAREAID
and
ctrans.accountnum = @ACCOUNTNUM
and ctrans.accountnum = custtable.accountnum
and ctrans.dataareaid = custtable.dataareaid

group by ctrans.accountnum, ctrans.dataareaid, custtable.name, custtable.blocked, custtable.accountnum, custtable.creditmax
) b
--START MOD
join simple_intlist_to_tbl (@ACCOUNTNUM) i on b.accountnum_ = i.numbers
--END MOD

THE FUNCTION

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[simple_intlist_to_tbl] (@list nvarchar(MAX))
RETURNS @tbl TABLE (numbers int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (numbers)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

NOW THE CALL TO THE SP:

exec sp_mySP '100124, 100144, 100253', @dataareaid = 'xxx'

The result set is empty! Logically it should work!

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-30 : 14:13:50

quote:
Originally posted by visakh16

nope it should work fine as long as you've only single avlues existing in accountnum field and you pass comma separated values through parameter.

How do I alter this with multiple values for the accountnum field! This field is a 6 digit number!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:16:58

does select * from simple_intlist_to_tbl (@ACCOUNTNUM) return you individual values correctly?

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-30 : 14:41:53

quote:
Originally posted by visakh16

does select * from simple_intlist_to_tbl (@ACCOUNTNUM) return you individual values correctly?

Yes,

select * from simple_intlist_to_tbl (10)

returns a table with a value 10.

select * from simple_intlist_to_tbl (10, 10)

too many arguments.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:51:07

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

does select * from simple_intlist_to_tbl (@ACCOUNTNUM) return you individual values correctly?

Yes,

select * from simple_intlist_to_tbl (10)

returns a table with a value 10.

select * from simple_intlist_to_tbl (10, 10)

too many arguments.....

for multiple values, pass it as

select * from simple_intlist_to_tbl ('10, 10')

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-30 : 16:30:03

Yes but I want multiple account numbers, meaning I need to pass @ACCOUNTNUM to the simple_intlist_to_tbl function and then use the , seperated "list" in the call to the SP!

exec sp_mySP '1001, 1002, 1003', @DATAAREAID = 'xxx'

I joined this line to my SP:

join simple_intlist_to_tbl (@ACCOUNTNUM) i on b.accountnum_ = i.numbers

Should I alter my SP to take away the @ACCOUNTNUM parameter so I can make calls to specific accounts???

For example:

join simple_intlist_to_tbl ('1001, 1002, 1003') i on b.accountnum_ = i.numbers

quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

does select * from simple_intlist_to_tbl (@ACCOUNTNUM) return you individual values correctly?

Yes,

select * from simple_intlist_to_tbl (10)

returns a table with a value 10.

select * from simple_intlist_to_tbl (10, 10)

too many arguments.....

for multiple values, pass it as

select * from simple_intlist_to_tbl ('10, 10')

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-30 : 16:31:17

visakh16 thank you!

What I did was alter the SP and eliminated the @ACCOUNTNUM parameter and used specific accountnum's for it. This gave me my results all in one table!!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources