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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.