stored procedures 
Author Message
 stored procedures

Hi all,

I am new to stored procedures so please bear with me..

I have created a new stored proc like this:


as select * from tbl_journal
where 0 = 0


go

My question is...I want to make the parameters optional, so that if one
value isn't passed, the stored proc will just ignore the clause for that
parameter.


not, I want the stored proc to execute:

select * from tbl_journal
where 0 = 0

Also, when I call the stored proc and pass only one of the parameters, I
get an error saying that it expected all parameters.

Thanks for any help.
Hammy

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!



Fri, 06 Jan 2006 20:39:43 GMT
 stored procedures

Here is one method that you can use:

CREATE PROCEDURE abc

AS


BEGIN
 select * from tbl_journal
 RETURN (0)
END


BEGIN

 RETURN (0)
END


BEGIN

 RETURN (0)
END


BEGIN

 RETURN (0)
END
GO

--
Keith, SQL Server MVP

Quote:

> Hi all,

> I am new to stored procedures so please bear with me..

> I have created a new stored proc like this:


> as select * from tbl_journal
> where 0 = 0


> go

> My question is...I want to make the parameters optional, so that if one
> value isn't passed, the stored proc will just ignore the clause for that
> parameter.


> not, I want the stored proc to execute:

> select * from tbl_journal
> where 0 = 0

> Also, when I call the stored proc and pass only one of the parameters, I
> get an error saying that it expected all parameters.

> Thanks for any help.
> Hammy

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Fri, 06 Jan 2006 20:49:42 GMT
 stored procedures

You can use defaults and then test them accordingly:

create procedure abc(


)
as select * from tbl_journal


go

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

Hi all,

I am new to stored procedures so please bear with me..

I have created a new stored proc like this:


as select * from tbl_journal
where 0 = 0


go

My question is...I want to make the parameters optional, so that if one
value isn't passed, the stored proc will just ignore the clause for that
parameter.


not, I want the stored proc to execute:

select * from tbl_journal
where 0 = 0

Also, when I call the stored proc and pass only one of the parameters, I
get an error saying that it expected all parameters.

Thanks for any help.
Hammy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 06 Jan 2006 20:46:41 GMT
 stored procedures
You can find an extensive discussion of this problem, including all the
performance implications, in this article by Erland Sommarskog:
http://www.algonet.se/~sommar/dyn-search.html


Quote:
> Hi all,

> I am new to stored procedures so please bear with me..

> I have created a new stored proc like this:


> as select * from tbl_journal
> where 0 = 0


> go

> My question is...I want to make the parameters optional, so that if one
> value isn't passed, the stored proc will just ignore the clause for that
> parameter.


> not, I want the stored proc to execute:

> select * from tbl_journal
> where 0 = 0

> Also, when I call the stored proc and pass only one of the parameters, I
> get an error saying that it expected all parameters.

> Thanks for any help.
> Hammy

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Fri, 06 Jan 2006 21:28:10 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Calling a Java Stored Procedure from another Java Stored Stored Procedure

2. Stored Procedure calling Stored Procedure

3. Executing a Stored Procedure in a Stored Procedure and selecting on the result

4. Call a stored procedure from another stored procedure

5. Calling a stored procedure with parameters from another stored procedure

6. using SQL stored procedure results in stored procedure

7. Using Resultset in Stored Procedure in another stored procedure

8. Stored Procedure using another Stored Procedure

9. Calling a Stored Procedure from a Stored Procedure

10. How to call an Oracle Stored procedure from MSSQL Server Stored Procedure

11. Calling a stored procedure with parameters from another stored procedure

12. Stored procedure calls multiple stored procedures


 
Powered by phpBB® Forum Software