473,837 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can a stored procedure parameter be optional

CK
I want the procedure to check for the existence of a paramter and if it is
there, it will process these instructions, otherwise it will process these
instructions. Any ideas? Thanks for your advice.

Regards,
CK
Mar 7 '06 #1
7 13366
SQL
optional parameters have to be at the end of a stored proc
CREATE PROCEDURE get_sales_for_t itle
@something int,
@title varchar(80) = NULL

AS

-- Validate the @title parameter.
IF @title IS NULL
BEGIN
print 'do something here'
END
ELSE
BEGIN
print 'do something else here'
END
now you can call this proc like this
exec get_sales_for_t itle 1
or like this
exec get_sales_for_t itle 1,2
you will see that the print statement will be different for the 2 calls

http://sqlservercode.blogspot.com/

Mar 7 '06 #2
Try:

create proc MyProc
(
@parm1 int -- mandatory
, @parm2 int = 5 -- optional
)
as
....

--
Tom

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

"CK" <c_**********@h otmail.com> wrote in message
news:sB******** ***********@new ssvr29.news.pro digy.net...
I want the procedure to check for the existence of a paramter and if it is
there, it will process these instructions, otherwise it will process these
instructions. Any ideas? Thanks for your advice.

Regards,
CK

Mar 7 '06 #3
CK (c_**********@h otmail.com) writes:
I want the procedure to check for the existence of a paramter and if it is
there, it will process these instructions, otherwise it will process these
instructions. Any ideas? Thanks for your advice.


Yes, consider:

CREATE PROCEDURE some_sp @a int,
@b int = 465 AS
PRINT @a + @b
go
EXEC some_sp 1

Prints 466. You can even say:

EXEC some_sp 1, DEFAULT

to explicitly say that you want the default value to be used.

The most commonly used default value for stored procedure parameters is
probably NULL.

Note that there is no way in the stored procedure to tell whether
the parameter was actually specified in the call, or whether the
default was used. That is, inside some_sp you cannot tell the
difference between

EXEC some_sp 1

and

EXEC some_sp 1, 465
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 7 '06 #4
Yes, as you have seen, but you might want to review your old Software
Engineering notes about coupling and cohesion in code modules.

Mar 8 '06 #5
>es, as you have seen, but you might want to review your old Software
Engineering notes about coupling and cohesion in code modules.

in your world on a cloudless day, what color is the sky?????

Mar 8 '06 #6
SQL (de********@gma il.com) writes:
optional parameters have to be at the end of a stored proc
CREATE PROCEDURE get_sales_for_t itle
@something int,
@title varchar(80) = NULL

AS


No, there is now such law. While it may be practical to have parameters
with default value at the end, this is perfectly legal:

CREATE PROCEDURE some_sp @x int = NULL, @u INT AS
...
go
EXEC some_sp @u = 123

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 8 '06 #7
SQL
True,
I should have been more specific and should have said that you have to
put them at the end if you want to call the proc with the parameters by
position instead of by name

CREATE PROCEDURE some_sp @x int = NULL, @u INT AS
select getdate()
go
EXEC some_sp @u = 123 --fine

EXEC some_sp 123 --will fail
http://sqlservercode.blogspot.com/

Mar 9 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
18612
by: Michael | last post by:
This one's really got me. I have a VB.NET (version 1.1.4322) project that provides an easy way to execute stored procedures on a generic level. When I run the code on computer A (running SQL Server 2000 version 08.00.0194) the code works great. However, computer B (running SQL Server 2000 version 08.00.0534) bombs when I try to execute the sproc saying 'Could not find stored procedure 'spmw_ReadByPage'. My thought process went as...
1
12131
by: Agoston Bejo | last post by:
Hi! I am trying to pass a VBScript Date variable as value to an ADO adDate parameter of a stored procedure (which runs in an SQL Server). If I add the parameter like this: oCmd.Parameters.Append oCmd.CreateParameter( "@p_Date", adDate, adParamInput, , dParam) where oCmd is of type ADODB.Command and dParam is a VBScript variable of type Date
1
1657
by: ganesh wayachal via SQLMonster.com | last post by:
In which system table the information about the optional parameters passed to stored procedure are stored. I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional. -- Message posted via http://www.sqlmonster.com
2
5730
by: Caro | last post by:
I have a stored procedure spGetAccessLogDynamic and when I try to call it I get the following error: Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'S'. I dont know why because I dont have anything refering to stored procedure 'S' I have ran my SQL String with sample values and it works fine. So I
2
5466
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
22
4298
by: M K | last post by:
Heres my SP: ( i am trying to add more than 1 field but get the same error no matter how many i try to add, i thought i would try to insert the primary key only and work up from there but the primary key insert didnt work either. I can run the SP manually in Visual Studio .NET and it works. When i try to do this it doesn't. Any help is greatly appreciated... newbie here as well so please be gentle :) ALTER PROCEDURE dbo.AddJob
2
2784
by: Dave Anderson | last post by:
I really like Bob's "stored procedure as method of Connection Object" technique. It is convenient, compact, and concise, and simplifies protection from SQL injection. HOWEVER, I cannot figure out a way to pass a null value to an INT parameter when I use this. Null string parameters are fine. The error reads: Microsoft OLE DB Provider for SQL Server error '80040e07' Operand type clash: text is incompatible with int
2
11521
by: syntego | last post by:
We commonly use triggers to log changes to our main tables to historical log tables. In the trigger, we create a concatenated string of the old values by casting them as follows: CAST(O.MYDATE AS CHAR(30)) When directly updating date fields in the main table, the logged value gets saved in the format YYYY-MM-DD as expected.
7
7076
by: jamesclose | last post by:
My problem is this (apologies if this is a little long ... hang in there): I can define a function in VB.NET with optional parameters that wraps a SQL procedure: Sub Test(Optional ByVal Arg1 As Integer = 0, _ Optional ByVal Arg2 As Integer = 0, _ Optional ByVal Arg3 As Integer = 0) ' Call my SQL proc with the same signature
0
9851
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9695
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10902
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10286
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9420
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4481
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.