May 5, 2008 at 9:15 am
I have several sp's to make changes to. They bring back a few pieces of data. Now we want to bring back this same data with 2 more parameters passed in. So...what is the cleanest, fastest way to add these two params to the list?
select * from Tables
--now this is being added
where @musicians = bass
--
and @color = red
or it could be where @musicians = drums or it could be where at musicians = ALL
and @color = and it could be all here too.....
is the if statements the way I need to go or is there a better way?
This is my first assignment and I want to look good in making these changes!!
thanks all.....I appreciate the quick help!
May 5, 2008 at 2:19 pm
You may try to build your string, then execute it.
DECLARE @var NVARCHAR(2000)
SET @var = 'SELECT * FROM Server_inventory WHERE INSTNC_NM = ''MSSQL1'''
EXEC sp_executesql @var
May 6, 2008 at 2:58 pm
If the parameter columns do not allow nulls then you can do the select using Coalesce. With the key part being the Where clause...
where region = Coalesce(@region,region) if @region is a parameter, then when it is null the clause becomes Where region = region" if not null then it is "Where region=@region"
declare @region char(1)
declare @tbl table (row int identity(1,1),region char(1))
set @region = null
insert into @tbl (Region)
select null union all
select 'A' union all
select 'D' union all
select 'B'
select * from @tbl
select * from @tbl
where region = Coalesce(@region,region)
set @region = 'A'
select * from @tbl
where region = Coalesce(@region,region)
Toni
** additional comments ** to add parameters tack on "And Parm2=Coalesce(@parm2,parm2) ... and on and on
May 6, 2008 at 3:34 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply