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?
Microsoft SQL Server

Avatar of undefined
Last Comment
Declan Basile

8/22/2022 - Mon
knightEknight

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

knightEknight

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
Christopher Kile

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Declan Basile

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)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Declan Basile

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.