Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Adding contents of a variable to a SQL Statement

I'm using SQL Server 2012 and have a stored procedure that returns a recordset from a Select statement, and the select statement is hundreds of characters long.  I want to add a parameter to this stored procedure to pass to it additional where clause text.  How can I append the contents of the parameter to the end of the SQL Statement to further filter out records returned from the stored procedure?
Avatar of knightEknight
knightEknight
Flag of United States of America image

declare your parameter variable with a default value of null:

  alter procedure myProcedureName
    @myParameter varchar(50)=NULL
  as

Open in new window



then in the WHERE clause of the SQL query, add something like the following:

 
and myField = isnull(@myParameter,myField)

Open in new window

Sorry, I think I mis-understood what you are asking for.  It looks like you want to pass in a segment of SQL code, correct?  My proposal above obviously will not do that.
ASKER CERTIFIED SOLUTION
Avatar of Christopher Kile
Christopher Kile
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Declan Basile

ASKER

I'm looking for a better option than dynamic SQL because the SQL has hundreds of characters.
If I do use dynamic SQL, does anyone see anything wrong with putting the static part of the SQL in a user defined function and doing something like ...

SQL = 'Select * from fnSomeFunctionName()' & @WhereClauseParameter
 exec sp_execuresql @SQL

Here is the Static part of the SQL ...

      SELECT Replace([Customers].[ShortName],'/','/ ') AS Customer,
      [CustomerOrders].[Number] + '-' + [COItems].[LineNo] + '-' +
      [CustomerReleases].[Number] AS CustInfo, WorkOrders.Number AS WO,
      Parents.ParentName,
      [Assemblies].[Number] +
      IsNull(', AssyRevLvl: ' + [Assemblies].[AssyRevLvl], '') +
      IsNull(', AssyRevDate: ' + Convert(varchar, [Assemblies].[AssyRevDate], 1),'') +
      IsNull(', BOMRevLvl: ' + [Assemblies].[BOMRevLvl], '') +
      IsNull(',  BOMRevDate: ' + Convert(varchar, [Assemblies].[BOMRevDate], 1), '') +
      IsNull(', ' + [Assemblies].[Name],'') AS Item,
      AltekReleases.Quantity AS Qty, AltekReleases.Date AS ShipDate,
      AltekReleases.Shipped, ShipMethods.ShipMethod, ShipCompanies.ShipCompany,
      CustomerReleases.Date AS DockDate, [Addresses].[City] + ', ' +
      [Addresses].[State] AS CityState, AltekReleases.AltekReleaseId,
      WorkOrders.WorkOrderId, Assemblies.AssemblyId, Customers.CustomerId,
      Customers.DaysEarly, Customers.DaysLate, Addresses.DaysToShip,
      CustomerReleases.HaltPS, CustomerReleases.AllowPD,
      CustomerReleases.OrSooner, CustomerReleases.TimeStamp,
      CustomerReleases.CustReleaseId,
      CASE WHEN CustomerReleases.HaltPS = -1 THEN -1
            WHEN Left([CustomerOrders].[Number], 4) = 'TEMP' THEN -1
            WHEN CHARINDEX('R', [WorkOrders].[Number]) = 0
            AND [CustomerReleases].[Price] = 0
            AND [CustomerReleases].[AllowZP] = 0 THEN -1
            ELSE 0
      END AS HaltMisc,
      CASE WHEN CHARINDEX('R', [WorkOrders].[Number]) <> 0 THEN 0
            WHEN [Customers].[DaysEarly] Is Null
            OR [Addresses].[DaysToShip] Is Null THEN 1
            WHEN
            dbo.fnWDDateAdd(-1*IsNull([Customers].[DaysEarly],0), [CustomerReleases].[Date])
            > dbo.fnWDDateAdd(
            IsNull([ShipMethods].[DaysToShip], IsNull([ShipCompanies].[DaysToShip],
            IsNull([Addresses].[DaysToShip],0)))
            , dbo.fnMyGetDate())
            AND CHARINDEX('R', [WorkOrders].[Number]) = 0
            AND [CustomerReleases].[OrSooner] = 0 THEN -1
            ELSE 0
      END AS HaltEarly,
        CASE WHEN CHARINDEX('R', [WorkOrders].[Number]) <> 0 THEN 0
            WHEN [Customers].[DaysLate] Is Null
            OR [Addresses].[DaysToShip] Is Null then 1
            WHEN
            dbo.fnWDDateAdd(IsNull([Customers].[DaysLate],0), [CustomerReleases].[Date])
            < dbo.fnWDDateAdd(
            IsNull([ShipMethods].[DaysToShip], IsNull([ShipCompanies].[DaysToShip],
            IsNull([Addresses].[DaysToShip],0)))
            , dbo.fnMyGetDate())
            AND CHARINDEX('R', [WorkOrders].[Number]) = 0
            AND [CustomerReleases].[AllowPD] = 0 Then -1
            ELSE 0
      END AS HaltLate
      FROM ((((((((((AltekReleases INNER JOIN CustomerReleases
      ON AltekReleases.CustReleaseId = CustomerReleases.CustReleaseId)
      INNER JOIN COItems ON CustomerReleases.COItemId = COItems.COItemId)
      INNER JOIN CustomerOrders ON COItems.COId = CustomerOrders.COid)
      INNER JOIN Customers ON CustomerOrders.CustomerId = Customers.CustomerId)
      INNER JOIN Assemblies
      ON CustomerReleases.AssemblyId = Assemblies.AssemblyId)
      INNER JOIN Parents ON Assemblies.ParentId = Parents.ParentId)
      INNER JOIN ShipMethods
      ON AltekReleases.ShipMethodId = ShipMethods.ShipMethodId)
      INNER JOIN ShipCompanies
      ON AltekReleases.ShipCompanyId = ShipCompanies.ShipCompanyId)
      INNER JOIN TempAltekReleases AS fn1
      ON AltekReleases.AltekReleaseId = fn1.AltekReleaseId)
      INNER JOIN Addresses
      ON AltekReleases.AddressId = Addresses.AddressId)
      INNER JOIN (WorkOrders INNER JOIN fnRelWOCOItemAll() AS fn2
      ON WorkOrders.WorkOrderId = fn2.WorkOrderId)
      ON COItems.COItemId = fn2.COItemId
      WHERE (CHARINDEX('R', [WorkOrders].[Number])=0
      AND fn1.ProgramSessionId = @PROGRAMSESSIONID)
      OR (CHARINDEX('R', [WorkOrders].[Number])<>0
      AND [CustomerReleases].[Number]<>'0000'
      AND fn1.ProgramSessionId = @PROGRAMSESSIONID)
I found out that you can't use temporary tables in user defined functions.  I ended up keeping the additional filtering in the front end program where is always has been and optimized it to work faster.  Thank you for your input.