Dear Friends!

I have a problem of executing the conditional query at runtime in the procedure.
i.e. I have one parameter in my procedure.Depending upon its values I have to concatenate a query and the complete procedure is to be executed.

Below is my procedure:

create procedure GetGiftCertificates

@Type varchar(50), @SubType varchar(50),@SearchBy varchar(50)=null,@SearchFor varchar(50)=null

as

declare @check as varchar(500)

if(@SubType='New')
begin
set @check='(DateDiff(d,GetDate(),ExpireDate)>=0 and Payconfirm=y)and redeem=n'
end

if (@SubType='Redeem')
begin
set @check=' (DateDiff(d,GetDate(),ExpireDate)<0 and Payconfirm=y)or redeem= y'
end

if(@SubType='Issued')
begin
set @check=' Payconfirm=Y and Issue=Y and redeem=n and ExpireDate is not null'
end

--Main procedure
begin
select
Gftid,Sname,Rname,Smail,Rmail,Address1,Address2,Ci ty,
State,Country,Zip,Value,PurDate,[ExpireDate],Giftcert,Message,
ShippingCharge,TotalAmount,UPSNO,CertificateType,R edeem,Issue from GiftCertificate
where CertificateType=@Type + @check order by gftid
end


my procedure is getting compiled properly but it is not giving me any records.

I have also attached script file with this message.

Please help me in this regard!

Regards!
Amol kash

Read more about dynamic execution of queries
See BOL topic "EXECUTE" (T-SQL) ,"sp_executesql" or "Building Statements at Run Time"
There is a lot of resources on this forum about it.