asked on

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

Jim Horn

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tim_cs

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)

Ali Saad

ASKER

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?

Jim Horn

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.