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 7 13374
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/
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
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
Yes, as you have seen, but you might want to review your old Software
Engineering notes about coupling and cohesion in code modules.
>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?????
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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
|
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
|
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
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
| |
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
|
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
|
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.
|
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
|
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...
|
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,...
| |
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |