Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Unanswered: Runtime execution of Query in SQL Procedure

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: Runtime execution of Query in SQL Procedure

    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.

    Code:
    
    
    alter procedure Quintex_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
    print @check
    --Main procedure    
    begin
     EXEC(
        'select
    	Gftid,Sname,Rname,Smail,Rmail,Address1,Address2,City, 
    	State,Country,Zip,Value,PurDate,[ExpireDate],Giftcert,Message,
    	ShippingCharge,TotalAmount,UPSNO,CertificateType,Redeem,Issue from GiftCertificate 
    	where CertificateType='+@Type +' AND '+ @check +' order by gftid'  
         )
    end  
    GO
    exec Quintex_GetGiftCertificates 'email','Issued' 
    
    

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •