Default Value for an integer Column as a paramater

Hello
Am using the follwing Proc
Create Proc  test (Col1 Nvarcahr(20) = '%' )
AS
Select  col1,col2 from mytable Where Col1 like Col1
--
The Above example is good to Return one Row if the user use
Exec Test 'Blabla'
Or many rows if he use
Exec Test

I need the same technique with an INTEGER Column
I means how to implement the above example if COL1 Integer ? because i cant use ' %' with the integer parameter

Thanks
ali_alannahAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Another option would be to have a single value represent all, like this...

Create Proc  test (@ID int = 0) AS
SELECT col1,col2
FROM mytable
WHERE (Col1 = @ID) OR (@ID =0)      
GO
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm not following your SQL, but here's an SP with an optional integer parameter

create proc test_sp(@mandatory as integer, @optional as integer = 0) as
SELECT @mandatory, @optional
GO

-- This returns 5, 0
exec test_sp 5

-- This returns 5, 6
exec test_sp 5, 6
0
 
tim_csCommented:
You would want to default the INT to NULL then in your where clause use one of these.

WHERE
   (yourValue = @yourINT OR @yourINT IS NULL)

OR

WHERE
   yourValue = COALESCE(@yourINT, yourValue)
0
 
ali_alannahAuthor Commented:
Hello Admin
By mistake i gave the points to jimhorn:
 the correct Answer was by tim_cs
How can i reassign it to tim_cs?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Did you try my first proposed solution?
Also, both my second solution and the one tim_cs posted is very identical.   I'm confident that both of my posts perform what you originally asked.

>How can i reassign it to tim_cs?
Ask a question in the Community Support TA to un-accept an answer so you can re-accept.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.