How to run store 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

General SQL Server Forums

New to SQL Server Programming

How to run store procedure

Author

Topic

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-24 : 08:49:20

Hi,
I have a store procedure like this.
alter procedure cCust @Banner char(3)
as
begin
delete from LCust
insert LCust select c.CustNum, c.CustName, c.SubB
From Bcustomr as c
where c.ActionCode <> 'D' and SubB IN (@Banner)
select custnum, custname, SubB from lcust
end

How do I run this store procedure. (with parameter IN)

exec cCust ('28', 'B1') it gave me an error.
(Incorrect syntax near '28')

if I run, exec cCust '28' it just gives me SubB 28. I need the store procedure that I could put some SubB in it.
Thanks in Advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 08:59:33

alter procedure cCust @Banner char(3)
as
begin
delete from LCust
insert LCust select c.CustNum, c.CustName, c.SubB
From Bcustomr as c
where c.ActionCode <> 'D' and ','+@Banner+',' like '%,'+SubB+',%'
select custnum, custname, SubB from lcust
end

Go

exec cCust '28,B1'

Madhivanan

Failing to plan is Planning to fail

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-24 : 09:17:30

I changed my procedure to where condition above.
It doesn't give me an error, but it doesn't give me the result.
I know the data is there.
Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 09:20:39

Change the datatype from char(3) to varchar(1000)

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-10-24 : 09:47:06

DYNAMIC SQL is needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:06:14

quote:
Originally posted by AndrewMurphy

DYNAMIC SQL is needed.

why? wont Madhi's sugestion work?

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-24 : 10:12:27

I changed the data type to varchar(1000)
It doesn't give the result.
What is dynamic sql??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 10:14:01

What is dynamic sql??
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-27 : 09:38:21

Hi Madhi,
I tried today by changed varchar(1000) it gave me all the data in the table.
I don't know why for some reason, when I posted on oct 24, it didn't give me the result.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 03:00:44

quote:
Originally posted by lily_wibisana

Hi Madhi,
I tried today by changed varchar(1000) it gave me all the data in the table.
I don't know why for some reason, when I posted on oct 24, it didn't give me the result.
Thanks

You might have forgot to put 1000

Madhivanan

Failing to plan is Planning to fail

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-29 : 09:00:07

Not. This is my store procedure. I copied exactly like your paramater.

alter procedure cCust @Banner varchar(1000)
as
begin
delete from LCust
insert LCust select c.CustNum, c.CustName, c.SubB, c.RepID
From Bcustomr as c
where c.ActionCode <> 'D' and ','+@Banner+',' like '%,'+SubB+',%'
select custnum, custname, subb, repid
from lcust
end
In other word I can't use "SELECT FROM WHERE IN @parameter" in store procedure?

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 09:11:17

<<
In other word I can't use "SELECT FROM WHERE IN @parameter" in store procedure?
>>

Yes

Madhivanan

Failing to plan is Planning to fail

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-29 : 09:21:46

Is there anyway to call store procedure like as above, and with multiple value parameter from reporting services?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:15:14

quote:
Originally posted by lily_wibisana

Is there anyway to call store procedure like as above, and with multiple value parameter from reporting services?
Thanks.

EXEC cCust @Banner='value1,value2,...'

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-29 : 13:18:35

Could you please more detail.
I am sorry I don't get how you exec the above statement in reporting services.
in data tab, with command type : Text (I have this below)
EXEC cCust @Banner
And in report parameter, multi value is checked.
when I ran the report with 1 value the result came out. but when I ran the report with multi value on it, it gave me an error message.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 13:26:56

use command type stored procedure and give name as cCust. rest it will take automatically while trying to run in data tab.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 13:29:22

also it would be more helpful if you can post error message too

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-29 : 13:41:10

For multiple values in a single parameter, I think u have to spilt the values into single value and then use ur query.There may be data type conversion error may occurs.

malay

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-29 : 13:52:56

The error message as follows
An error occurred during local report processing
an error has occurred during report processing
query execution failed to data set 'MyData'
must declare the scalar variable "@Banner"

I used command type store procedure as you mentioned above, when I ran the report it just printed 1 banner, even I checked on some banners.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 03:48:25

quote:
Originally posted by lily_wibisana

The error message as follows
An error occurred during local report processing
an error has occurred during report processing
query execution failed to data set 'MyData'
must declare the scalar variable "@Banner"

I used command type store procedure as you mentioned above, when I ran the report it just printed 1 banner, even I checked on some banners.

Thanks.

this means you'vent properly declared the parameter @banner in procedure.
do you mean it did show multivalues when report was rendered? are you using any grouping in report?

lily_wibisana
Starting Member

29 Posts

Posted - 2008-10-30 : 09:02:09

My store procedure
alter procedure cCust @Banner char(3)
as
begin
delete from LCust
insert LCust select c.CustNum, c.CustName, c.SubB
From Bcustomr as c
where c.ActionCode <> 'D' and SubB = @Banner
select custnum, custname, SubB from lcust
end

But I need multivalue in reporting services parameter.
and I got above error message.
Thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources