Database Discussion - SQL Server Stored Proc Help Needed

Print Page | Close Window

SQL Server Stored Proc Help Needed

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=9059
Printed Date: 30 March 2026 at 7:36am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Server Stored Proc Help Needed

Posted By: Gary
Subject: SQL Server Stored Proc Help Needed
Date Posted: 20 January 2004 at 4:57am

Hi,

Working with asp and SQL Server, I want to have a dynamic stored proucedure that can accept 2 values, but will also work with 1 value...

CREATE PROCEDURE [mySP]
   @value1 nvarchar(7),
   @value2 nvarchar(7)
AS
    SELECT *
    FROM myTable
    WHERE [col1] = @value1 AND [col2] = value2

Easy enough if both values are provided (eg mySP 'x', 'y'). However, I may not be passing the second value (eg mySP 'x'), but then the sp throughs an error as it expects 2 values.

How can this be achieved ???
Thx

Replies:

Posted By: gavinc
Date Posted: 20 January 2004 at 9:21am

Gary wrote:

CREATE PROCEDURE [mySP]
   @value1 nvarchar(7),
   @value2 nvarchar(7)
AS
    SELECT *
    FROM myTable
    WHERE [col1] = @value1 AND [col2] = value2

What you can do is to assign a default value to the optional parameter:

CREATE Procedure sp_MyProc @Val1 varchar(5), @Val2 varchar(5) = NULL AS etc....

but you will probably have to change the code of the procedure so that it runs one of two select queries depenind on the value of the option parameters since i'm not 100% sure if you can say:

WHERE Col1 = @Val1 AND Col2 = @Val2 if @Val2 is NULL

I think you will have to say:

WHERE Col1 = @Val1 AND Col2 IS NULL

Posted By: Mart
Date Posted: 20 January 2004 at 12:53pm

Try



CREATE PROCEDURE [mySP]

    @value1 nvarchar(7),

    @value2 nvarchar(7) = NULL

AS

    SELECT *

    FROM myTable

    WHERE [col1] = @value1 AND [col2] = value2

Posted By: Flamewave
Date Posted: 21 January 2004 at 12:18am


CREATE PROCEDURE dbo.mySP
    @value1 nvarchar(7),
    @value2 nvarchar(7)
AS
If @value2 is null or @value2=''
    Select * From myTable Where col1 = @value1
Else
    Select * From myTable Where col1 = @value1 and col2 = @value2

Then if you dont want to use the second value, pass it as NULL
exec dbo.mysp 'value1', null

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: psycotik
Date Posted: 21 January 2004 at 1:55am

CREATE PROCEDURE [mySP]
   @value1 nvarchar(7),
   @value2 nvarchar(7) = Null
AS
    SELECT *
    FROM myTable
    WHERE [col1] = @value1 AND ([col2] = value2 OR @value2 is Null)