Hi I want to create a simple stored proecudre which does the following:
Psudocode
@tempSelect = "SELECT * FROM Table"
if (@Param is NULL) then
exec @tempSelect
else
exec @tempSelect + ' WHERE id = ' + @Param + '
Is this method efficent? Thank you.
Try
select *
from table
where id=isnull(@param, id)
Select * from Table Where (ID = @Param or @Param is null)
Or
Select * from Table Where ID=Coalesce(@Param, ID)
[And if you are aiming for efficiency, replace * with the specific field you want to return.]
Yes - I certainly see nothing wrong with it. You could make it even simpler though:
Set NOCOUNT On;
if (@Param is NULL)
Select * From Table;
else
Select * From Table Where (ID=@Param);
Note: I'd probably spell out the fields, though.
Depending on the case, I would probably use dynamic SQL.
However you need to remember about SQL injection in case @param originates from a user, thats why you should never add a parameter directly to your sql.
In t-sql it would look something like (out of my head and untested ;):
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
SELECT ...
FROM table t
WHERE 1 = 1' (
IF(@param IS NOT NULL)
SET @SQL = @SQL + '
AND t.id = @id'
... possibly more things added to the query ...
EXEC sp_executesql
@SQL
, '@id AS INT'
, @id = @Param
By doing this, you will get an optimized query plan for each case (and by using sp_executesql, the query cache will be used as well)
I would especially avoid the OR solution, if you check the query plans generated with the OR compared to one without, you will understand why.
Try this code:
CREATE PROCEDURE [dbo].[ProcedureName]
@Param varchar(50)
AS
BEGIN
declare @tempSelect nvarchar(max)
SET NOCOUNT ON;
set @tempSelect = 'SELECT Col1, Col2 FROM Table where Col1 <> '' '
if @Param <> ''
begin
set @resultSet = @resultSet + ''' and Col1='''+@Param1
end
EXEC(@resultSet)
END
