stored procedure default value for int datatype parameter 2

[leahyj]

Hi,

I want to add a default value to a stored procedure parameter (datatype int), that when executing the stored procedure will grab all rows if that parameter is not passed a value to it.

If possible, I don't want to add logic, i.e. If @PassID = NULL, etc.

Is there a way to use a wildcard?

Do I have to CAST it first?

Example

GO
ALTER PROCEDURE [dbo].[myprocname] 
	-- Add the parameters for the stored procedure here
	@PassedID int = '> 0'

AS
BEGIN

SELECT *
FROM TABLE1
WHERE TABLE1.ThisID = @PassedID

 

[Qik3Coder]

you would have to code for a wildcard type value:
such as:

ALTER PROCEDURE [dbo].[myprocname] 
    -- Add the parameters for the stored procedure here
    @PassedID int
AS
BEGIN
[b]Declare @MyBooleanFlag bit
if @int1 is null
 begin
  set @MyBooleanFlag = 1
 end
[/b]
SELECT *
FROM TABLE1
WHERE
TABLE1.ThisID = 
 [b]CASE WHEN @MyBooleanFlag = 1 
   THEN table1.ThisID 
   ELSE @PassedID END[/b]

-The answer to your problem may not be the answer to your question.

 

[gmmastros]

Qik3Coder

It's possible that I don't understand the implications of what you are saying, but I suspect your code would suffer from NULL issues.

Declare @Temp Table(Data Int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(3)
Insert Into @Temp Values(NULL)

Declare @PassedId Int
Declare @MyBooleanFlag bit
if @PassedId is null
 begin
  set @MyBooleanFlag = 1
 end

Select * from @Temp
Where Data = Case When @MyBooleanFlag = 1
                  Then Data
                  Else @PassedId
                  End

Notice that the row where Data is NULL is NOT returned with this query.

Instead, you could use this syntax to return all the records.

Declare @Temp Table(Data Int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(3)
Insert Into @Temp Values(NULL)

Declare @PassedId Int

Select * 
from   @Temp
Where  (@PassedId Is NULL Or Data = @PassedId)

-George

"the screen with the little boxes in the window." - Moron

 

[leahyj]

Hi Qik3Coder,

Select * 
from   @Temp
Where  (@PassedId Is NULL Or Data = @PassedId)

was what I was looking for. Thanks.

I also came up with this solution

GO
ALTER PROCEDURE [dbo].[myprocname] 
    -- Add the parameters for the stored procedure here
    @PassedID varchar(10) = '%'

AS
BEGIN

SELECT *
FROM TABLE1
WHERE TABLE1.ThisID Like @PassedID

Do you have any thoughts on which one of the two is better, more efficient, etc. ?

Thanks again

 

[gmmastros]

Use the first one. The second also suffers from the NULL problem.

Ex:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)

[COLOR=blue]Declare[/color] @PassedId [COLOR=blue]Int[/color]

[COLOR=blue]Select[/color] * 
[COLOR=blue]from[/color]   @Temp
[COLOR=blue]Where[/color]  (@PassedId [COLOR=blue]Is[/color] NULL Or Data = @PassedId)

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  Data Like [COLOR=red]'%'[/color]

Notice when you run this code that the first select returns the NULL record where the 2nd query does not.

-George

"the screen with the little boxes in the window." - Moron

 

[Qik3Coder]

leahjy, if gmastros answered your question, then give him a star...

-The answer to your problem may not be the answer to your question.

 

[leahyj]

I did, but thanks for you input too.

 

[leahyj]

Hey gmastros,

Question:

what would be the difference between,

Where  (@PassedId Is NULL Or Data = @PassedId)

and

Where  (Data Is NULL Or Data = @PassedId)

The first one works, but I can't figure out why.

Thanks

 

[gmmastros]

Well... I'm glad you asked. This means you are paying attention and truly want to learn. Congratulations. I wish everyone were like that.

First... you need to consider the logic. Here, we are using OR.

So, if the expression on either side of the OR is true, the records will be returned. @Passed is a scalar variable (meaning that it can only contain 1 value). If that expression is TRUE, then the total expression will always evaluate to true.

Ex:

Where (1=1 or 'cat' = 'dog')

Since it's an OR, and 1 does equal 1, every record will be returned.

In the second example, you are comparing the data in individual records to see if it's NULL.

Does this make sense?


-George

"the screen with the little boxes in the window." - Moron

 

[Qik3Coder]

If it makes sense to flip this around:

Where  (@PassedId Is NULL Or Data = @PassedId)

To instead read:

Where  (Data = @PassedId Or @PassedId Is NULL)

it means where the data equals the value i asked for or else "give me everything", because i asked for nothing.


your second example doesnt work because you will never have an "id" column with a null value (i hope)


an easier to understand example may be:

TABLE1.ThisID = isNull(@passedID,table1.ThisID)

-The answer to your problem may not be the answer to your question.

 

[leahyj]

gmmastros and qik3coder,

It is sinking in thanks.

I used to use this quite a bit, in MS Access when I would program a forms parameter to show all records from a query if the user didn't make a choice on the forms choice.

What throws me off is that I thought I used to design the query to someting like

"select * from table1 where id = [forms.parameter] or id is null"

I guess it really was

"select * from table1 where id = [forms.parameter] or [forms.parameter] is null"

Thanks again.