none
Building where clause dynamically in stored procedure

    Question

  •  

    Hello All,

     

    I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria.

     

    WhereClause from UI: whereClause="where DefectImpact='High'"

     

    SQL Query in SP: SELECT @sql='select * from tablename'

    Exec(@sql + @whereClause )

     

    Here i am not able to get the results based on the search criteria. Instead i am getting all the results.

     

    Please help me in this regard.

     

    Thanks,

    Subba Rao.

     

    Friday, February 08, 2008 1:04 AM

Answers

  • Friday, February 08, 2008 2:30 AM
    Moderator
  • The link that Madhu posted is excellent and any database programmer who intends to work with dynamic sql SHOULD read it.

     

    What is the error you are getting with your query? I guess you dont have an extra space between your query and where clause. Try adding a space after the query as:

     

    EXEC(@sql + ' ' + @whereClause)

     

    Friday, February 08, 2008 7:11 AM
    Moderator
  •  

    Well, you made some mistakes within your code, I will briefly outline this with some comments:

     

    Set @whereClause = '' --Otherwise this will be NULL and NULL added to string will be null

    if @DefImpact <> '' OR @DefImpact IS NULL --YOu probably also want to handle the NULL value if paased in explictly, right ?

          Set @whereClause = ' where DefImpact = ' QUOTENAME(@DefImpact,'''')

     

    set @sql =@sql + @WhereClause -- YOu have the wrong variable to assign to

     

    IF @Debug = 0x1

    PRINT @sql --I also use a separate parameter which is by default false to allow me to print the statements out if needed

     

    EXEC(@sqlWhere) --Put parantheses arounf the statement, otherwise I will be interpreted as a stored procedure

    End

     

     

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Saturday, February 09, 2008 10:42 PM
    Moderator

All replies

  • Friday, February 08, 2008 2:30 AM
    Moderator
  • The link that Madhu posted is excellent and any database programmer who intends to work with dynamic sql SHOULD read it.

     

    What is the error you are getting with your query? I guess you dont have an extra space between your query and where clause. Try adding a space after the query as:

     

    EXEC(@sql + ' ' + @whereClause)

     

    Friday, February 08, 2008 7:11 AM
    Moderator
  • Thanks Madhu and Jacob.

     

    I have seen that article 2 days before, using that i have built the SP like:

    @DefApp nvarchar(255)= '' ,

    @DefBusFunction nvarchar(255)= '' ,

    @DefImpact nvarchar(255) = '',

    AS

     

    Begin

     

    declare @sql nvarchar(4000)

    declare @whereClause nvarchar(4000)

    DECLARE     @return_value int

    declare @sqlWhere nvarchar(4000)

     

    select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)'

     

    if @DefImpact <> ''

          Set @whereClause = ' where DefImpact '

          SET @whereClause = @whereClause + ' = ''' + @DefImpact + ''''

    set @sqlWhere=@sql + @WhereClause

    EXEC @sqlWhere

    End

     

    and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like:

    sqlCmd = new SqlCommand("subbusample", conn);

    sqlCmd.CommandType = CommandType.StoredProcedure;

    sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString()));

    sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString()));

    sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString()));

     

    RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

    RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");

    RptViewer.ShowParameterPrompts = false;

    RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";

     

    when i execute it, i am not able to fill the report with data.

     

    Please let me know what i am doing wrong.

     

    Thanks,

    SR.

    Friday, February 08, 2008 5:41 PM
  • do you get any error? When we debug Dynamic sql , best method is to print in each step and see the sql and run the sql in query analyser and check whether its returning rows or not. When  you run this sp in QA with input parameter does it return rows?

     

    Madhu

     

    Saturday, February 09, 2008 2:45 AM
    Moderator
  •  

    Well, you made some mistakes within your code, I will briefly outline this with some comments:

     

    Set @whereClause = '' --Otherwise this will be NULL and NULL added to string will be null

    if @DefImpact <> '' OR @DefImpact IS NULL --YOu probably also want to handle the NULL value if paased in explictly, right ?

          Set @whereClause = ' where DefImpact = ' QUOTENAME(@DefImpact,'''')

     

    set @sql =@sql + @WhereClause -- YOu have the wrong variable to assign to

     

    IF @Debug = 0x1

    PRINT @sql --I also use a separate parameter which is by default false to allow me to print the statements out if needed

     

    EXEC(@sqlWhere) --Put parantheses arounf the statement, otherwise I will be interpreted as a stored procedure

    End

     

     

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Saturday, February 09, 2008 10:42 PM
    Moderator
  • No Madhu, i am not getting any error, @defectImpact value which i am passing from UI, is not holding the value in SP.

     

    Is anything i am doing wrong?

     

    Jens:

    I tried in that way also. no luck is there any mistake in UI code?

     

    Thanks,

    SR.

     

    Monday, February 11, 2008 2:44 PM
  • Moving to Transact-SQL forum, more appropriate venue for dealing with this particular issue.

     

    Good luck,

     

    John

     

    Tuesday, February 12, 2008 11:23 PM
  • I would think you have a null variable coming in. I find that I have to wrap null checks something like this:


    declare @piggy nvarchar(32); 

    set @piggy = '' if not isnull(@piggy,'')=''

      print 'it neither null nor blank.'


     



    MP

    Thursday, October 11, 2012 1:20 AM