• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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?
2 Solutions
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now