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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

declare your parameter variable with a default value of null:

  alter procedure myProcedureName
    @myParameter varchar(50)=NULL

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.
Christopher KileCommented:
You will not be able to do this with a static SQL statement; by definition, static SQL within a stored procedure may not be changed at runtime.  You either have to build a WHERE clause that recognizes the parameter when present but otherwise returns True when the parameter is absent, or you have to construct your query as a dynamic SQL statement and insert your WHERE component into the dynamic SQL before executing it.  What type of clause are you trying to add to the query?  That could make a difference in which approach you choose, so it would help if you could answer with as much detail as possible.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(similar to kEk's answer) Perhaps, if the number of columns that you're talking about is limited, you can pass an optional parameter, like this..
CREATE PROC sp_yourproc(@fruit_id int = NULL) AS

SELECT blah, blah, blah
FROM YourTable
WHERE (fruit_id = @fruit_id OR @fruit_id IS NULL)

Open in new window

OR, just throw in the towel and use dynamic SQL..

exec sp_yourproc 'fruit_id = 7'

CREATE PROC sp_yourproc(@where varchar(1000)) AS

declare @sSQL as string 
sSQL = 'SELECT blah, blah, blah FROM YourTable WHERE ' + @where
exec sp_executesql @sql

Open in new window

Declan_BasileITAuthor Commented:
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,
      [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,
      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
            dbo.fnWDDateAdd(-1*IsNull([Customers].[DaysEarly],0), [CustomerReleases].[Date])
            > dbo.fnWDDateAdd(
            IsNull([ShipMethods].[DaysToShip], IsNull([ShipCompanies].[DaysToShip],
            , 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
            dbo.fnWDDateAdd(IsNull([Customers].[DaysLate],0), [CustomerReleases].[Date])
            < dbo.fnWDDateAdd(
            IsNull([ShipMethods].[DaysToShip], IsNull([ShipCompanies].[DaysToShip],
            , 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)
Declan_BasileITAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.