CODEWITHSUNDEEP
sqlsql-server-2005t-sqlstored-procedures
kacalapy
kacalapy

Reputation: 10164

conditional where clause based on a stored procedure parameter?

I have a SQL Server 2005 stored proc that takes a parameter: @includeClosedProjects.

There's a WHERE clause that I want to control based on this param.

create proc sel_projects
(@incClosedRel int = 1)
as

SELECT projectId, projectName
FROM project
WHERE CompletionStatusCID NOT IN (34, 35) <-- controlled by @incClosedRel

I want to get all projects (exclude the where clause), when @incClosedRel =1. Otherwise, include the where clause.

Upvotes: 6

Views: 3236

Answers (2)

serhat_pehlivanoglu
serhat_pehlivanoglu

Reputation: 982

create proc yourproc
@value int
as

if @value = 1
begin
-- your select query
end
else
begin
--your other select query
end

Upvotes: 1

Thomas
Thomas

Reputation: 64674

SELECT projectId, projectName
FROM project
WHERE CompletionStatusCID NOT IN (34, 35) 
    Or @incClosedRel = 1

Upvotes: 10

Related Questions