CODEWITHSUNDEEP
sqlwhere-clausesap-ase
Kirill Pashkov
Kirill Pashkov

Reputation: 3236

Return whole record set if no parameter were provided

I am making a stored procedure that would accept ID as a parameter and will bring one record related to the ID provided and if no ID were provided bring the whole result set back.

I have managed to make some workaround with COALESCE function but I really would like to have that input parameter as INTEGER data type.

Here's my code:

CREATE PROCEDURE GetThis
@ID VARCHAR(25) =   NULL
AS
BEGIN
SET @ID= COALESCE( @ID, '%' ) 
...

SELECT * FROM #TABLE
WHERE CONVERT(VARCHAR(25),ID) LIKE @ID
END

What is the best way to achieve this?

Upvotes: 0

Views: 22

Answers (1)

Siyual
Siyual

Reputation: 16917

Use an OR statement:

SELECT * FROM #TABLE
WHERE  @ID IS NULL
OR     CONVERT(VARCHAR(25),ID) LIKE @ID

Upvotes: 1

Related Questions