KirillPashkov 5 days ago

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?

Answers


Siyual 5 days ago

Use an OR statement:

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

Post Status

Asked in 5 days ago
Viewed 1,097 times
Voted 11
Answered 1 times

Search




Leave an answer