p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: Stored Procedures


Message #1 by arshad siddiqui <ash_arshad@y...> on Mon, 3 Dec 2001 20:44:43 -0800 (PST)
Hi

Is it possible to pass only few  parameters not all of

the parameters in Stored procedure,then what should 

be used for the parameters that I dont want to pass.

For example:Suppose the stored procedure is:

CREATE PROC nspStockLevelBalance (

@as_contract_start                   char(18),

@as_contract_end                     char(18),

@as_storer_start                     char(15),

@as_storer_end                       char(15),

@as_loc_start                        char(15),

@as_loc_end                          char(15),

@ad_cutoffdate                       datetime,

@as_warehouseref_start               char(18),

@as_warehouseref_end                 char(18)

) AS

BEGIN

-------------------

I want to pass only firt four parameters only.for that

I am using:

ssql="nspStockLevelBalance '"& clientstart &"','"&

clientend &"','"& storerstart &"','"& storerend

&"',NULL,NULL,'"& date1 &"',NULL,NULL"

set rss=cn.Execute (ssql)

But this is not returning any value means search does

not find any return value.I suppose I shall give some

other value may be null string like '' in place of

NULL.

Any clue.

Thanx

--Arshad--





__________________________________________________

Do You Yahoo!?

Buy the perfect holiday gifts at Yahoo! Shopping.

http://shopping.yahoo.com

Message #2 by David Cameron <dcameron@i...> on Tue, 4 Dec 2001 15:49:05 +1100
This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C17C7F.00ADD96E

Content-Type: text/plain



Write your procedure to deal with the NULL values. Then you won't have a

problem. If these are going into a WHERE clause there is one trick worth

knowing:



WHERE (@Val1 IS NULL OR MyField = @Val1) etc...



Also are you _sure_ you want to use char? Char means that the values will be

padded by spaces eg "Test" becomes "Test      " (6 spaces) for Char(10).



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: arshad siddiqui [mailto:ash_arshad@y...]

Sent: Tuesday, 4 December 2001 2:45 PM

To: ASP Databases

Subject: [asp_databases] Stored Procedures





Hi

Is it possible to pass only few  parameters not all of

the parameters in Stored procedure,then what should 

be used for the parameters that I dont want to pass.

For example:Suppose the stored procedure is:

CREATE PROC nspStockLevelBalance (

@as_contract_start                   char(18),

@as_contract_end                     char(18),

@as_storer_start                     char(15),

@as_storer_end                       char(15),

@as_loc_start                        char(15),

@as_loc_end                          char(15),

@ad_cutoffdate                       datetime,

@as_warehouseref_start               char(18),

@as_warehouseref_end                 char(18)

) AS

BEGIN

-------------------

I want to pass only firt four parameters only.for that

I am using:

ssql="nspStockLevelBalance '"& clientstart &"','"&

clientend &"','"& storerstart &"','"& storerend

&"',NULL,NULL,'"& date1 &"',NULL,NULL"

set rss=cn.Execute (ssql)

But this is not returning any value means search does

not find any return value.I suppose I shall give some

other value may be null string like '' in place of

NULL.

Any clue.

Thanx

--Arshad--





__________________________________________________

Do You Yahoo!?

Buy the perfect holiday gifts at Yahoo! Shopping.

http://shopping.yahoo.com



 




$subst('Email.Unsub')




Message #3 by "Ken Schaefer" <ken@a...> on Tue, 4 Dec 2001 16:09:04 +1100
Goto:



Start | Programs | Microsoft SQL Server | Books Online



Switch to the index and type in "Stored Procedure", Select "Creating" and

choose the first article (Create Procedure (Transact-SQL Reference))



Look at the "=default" option for specifying parameters in your CREATE PROC

statement. (I've already mentioned this)



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "arshad siddiqui" <ash_arshad@y...>

Subject: [asp_databases] Stored Procedures





: Hi

: Is it possible to pass only few  parameters not all of

: the parameters in Stored procedure,then what should

: be used for the parameters that I dont want to pass.

: For example:Suppose the stored procedure is:

: CREATE PROC nspStockLevelBalance (

: @as_contract_start                   char(18),

: @as_contract_end                     char(18),

: @as_storer_start                     char(15),

: @as_storer_end                       char(15),

: @as_loc_start                        char(15),

: @as_loc_end                          char(15),

: @ad_cutoffdate                       datetime,

: @as_warehouseref_start               char(18),

: @as_warehouseref_end                 char(18)

: ) AS

: BEGIN

: -------------------

: I want to pass only firt four parameters only.for that

: I am using:

: ssql="nspStockLevelBalance '"& clientstart &"','"&

: clientend &"','"& storerstart &"','"& storerend

: &"',NULL,NULL,'"& date1 &"',NULL,NULL"

: set rss=cn.Execute (ssql)



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Message #4 by arshad siddiqui <ash_arshad@y...> on Mon, 3 Dec 2001 21:15:31 -0800 (PST)
I cant change the stored procedure as this is used for

some other application also.

please tell me some other way to do this.

thanx

Regards

--Arshad--





__________________________________________________

Do You Yahoo!?

Buy the perfect holiday gifts at Yahoo! Shopping.

http://shopping.yahoo.com

Message #5 by David Cameron <dcameron@i...> on Tue, 4 Dec 2001 17:24:37 +1100
This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C17C8C.5964F01C

Content-Type: text/plain



You *can't*.



Either you use a proc which you can edit or you generate a SQL SELECT

statement in your web page. Other less savoury options are available (eg

using the existing proc and performing a filter on it) but those are your

two best options.



You could create a new proc to do the work. That would mean you wouldn't

have to mess with the old one.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: arshad siddiqui [mailto:ash_arshad@y...]

Sent: Tuesday, 4 December 2001 3:16 PM

To: ASP Databases

Subject: [asp_databases] RE: Stored Procedures





I cant change the stored procedure as this is used for

some other application also.

please tell me some other way to do this.

thanx

Regards

--Arshad--





__________________________________________________

Do You Yahoo!?

Buy the perfect holiday gifts at Yahoo! Shopping.

http://shopping.yahoo.com



 




$subst('Email.Unsub')





  Return to Index