CODEWITHSUNDEEP
sqlsql-serverstored-procedureswhere-clause
Liam neesan
Liam neesan

Reputation: 2551

How to set stored procedure parameter for may or may not pass the values by user?

User may or may not pass the values in stored procedure values in SQL Stored Procedure

So how to declare my variable in StoredProcedure and how to define in Where clause?

Example

CREATE PROC spGetRecord
@Storeid nchar(10)=null
AS
BEGIN
    SELECT ID,NAME
    FROM table1
    WHERE StoreID=@Storeid    //@Storeid is may pass the value by user or may not
END

So how should I define @Storeid in Where clause

Thanks

Upvotes: 2

Views: 400

Answers (1)

SqlZim
SqlZim

Reputation: 38023

I am guessing you are building some sort of search procedure...

A simple start, check that the parameter either matches Storeid, or that the parameter is null:

create proc spGetRecord (@Storeid nchar(10)=null) as
begin
  set nocount, xact_abort on;

  select id,name
  from table1
  where (Storeid=@Storeid or @Storeid is null)
end;

For more advanced catch-all search queries, see the following:

Upvotes: 2

Related Questions