Starting with Dynamic SQL (Select problem)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Starting with Dynamic SQL (Select problem)

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-13 : 13:31:58

I have a dynamic SQL SP that its ‘Select’ statement stars thus:


    Select @sql = 
      'Declare @offer_date As datetime, @Id As bigint
       SELECT @offer_date = offe.offer_date, @Id = offe.Offer_id
       From Offers As offe

       ...

And I receive an error from app that says: You must declare the variable ‘@offer_date’. And I already declared it.. What is wrong?

Here is the complete structure:


USE market3
GO
ALTER PROCEDURE findOf_dyn
@CurrentPage int, @PageSize int, @Family smallint, @Product smallint, ..
As

BEGIN 
    SET NOCOUNT ON 
 
    Declare @sql nvarchar(4000), @param_list nvarchar(4000), 
     @Max_row int, @First_row int

     SET @Max_row = @PageSize * @CurrentPage  
     SET @First_row = @Max_row - (@PageSize - 1)   



    SET ROWCOUNT @First_row 
   
    Select @sql = 
      'Declare @Offer_date As datetime, @Id As bigint
       SELECT @Offer_date = offe.Offer_date, @Id = offe.Offer_id
       From Offers As offe

       LEFT JOIN Products_name As prod   
       On offe.Product_num = prod.Product_Id
       LEFT JOIN Families_product As fam
       On prod.Family_num = fam.Family_Id
       LEFT JOIN Offers_quality As of_qual  
       On offe.Offer_id = of_qual.Offer_num   
       LEFT JOIN Offers_rippening As of_rip  
       On of_qual.Offer_qual_id = of_rip.Offer_qual_num 

       WHERE 1 = 1'


    If @Family <> 0  
       SELECT @sql = @sql + ' AND (fam.Family_Id = @xFamily Or (x@Family = 9 AND offe.Other_families IS NOT NULL))'

    If @Product <> 0  
       SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'

 

    SELECT @sql = @sql + ' Order by offe.Offer_date Desc, offe.Offer_id Desc'  

    SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, ...'
    
    EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, ...

...

What I have to do to SELECT @Offer_date = offe.Offer_date, @Id = offe.Offer_id inside ‘@sql’? I need these variables (@Offer_date and @Id) to use it later in the SP

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-13 : 14:09:07

To set these variables, you'll need to use OUTPUT option of sp_executesql. Here is an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41413

Tara

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-14 : 04:25:17

You are right Tara

, I tried this (in bold), and now at least none error is returned from app..


USE market3
GO
ALTER PROCEDURE findOf_dyn
@CurrentPage int, @PageSize int, @Family smallint, @Product smallint, ..
As

BEGIN 
    SET NOCOUNT ON 
 
    Declare @sql nvarchar(4000), @param_list nvarchar(4000),  
     @Max_row int, @First_row int

     SET @Max_row = @PageSize * @CurrentPage  
     SET @First_row = @Max_row - (@PageSize - 1)   



    SET ROWCOUNT @First_row 

    Declare @Offer_date nvarchar(1000), @Id nvarchar(1000)   

    Select @sql = 
      'SELECT’ + @Offer_date + ‘= offe.Offer_date,’ + @Id + ‘= offe.Offer_id
       From Offers As offe

       LEFT JOIN Products_name As prod   
       On offe.Product_num = prod.Product_Id
       LEFT JOIN Families_product As fam
       On prod.Family_num = fam.Family_Id
       LEFT JOIN Offers_quality As of_qual  
       On offe.Offer_id = of_qual.Offer_num   
       LEFT JOIN Offers_rippening As of_rip  
       On of_qual.Offer_qual_id = of_rip.Offer_qual_num 

       WHERE 1 = 1'


    If @Family <> 0  
       SELECT @sql = @sql + ' AND (fam.Family_Id = @xFamily Or (x@Family = 9 AND offe.Other_families IS NOT NULL))'

    If @Product <> 0  
       SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'



 

    SELECT @sql = @sql + ' Order by offe.Offer_date Desc, offe.Offer_id Desc'  

    SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @Offer_date nvarchar(1000) output, @Id nvarchar(1000) output ...'
    
    EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @Offer_date output, @Id output   

...

And later (in the same SP) I use them here:


..
      WHERE 1 = 1 AND ((offe.Offer_date <’+ @Offer_date +’) Or (offe.Offer_date =’ + @Offer_date +’ And offe.Offer_id <=’ + @Id  +’))'

Thank you

The problem now is none record is returned in any case, but first problem is solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources