Passing SQL stored procedure entirety of WHERE clause
I have a SQL stored procedure of the form
SELECT [fields] FROM [table] WHERE @whereSql
I want to pass the procedure an argument (@whereSql) which specifies the entire WHERE clause, but the following error is returned:
An expression of non-boolean type specified in a context where a condition is expected
Can this be done?
The short answer is that you can't do it like this -- SQL Server looks at the contents of a variable as a VALUE. It doesn't dynamically build up the string to execute (which is why this is the correct way to avoid SQL injection attacks).
You should make every effort to avoid a dynamic WHERE as you're trying to do, largely for this reason, but also for the sake of efficiency. Instead, try to build up the WHERE clause so that it short-circuits pieces with lots of ORs, depending on the situation.
If there's no way around it, you can still build a string of your own assembled from the pieces of the command, and then EXEC it.
So you could do this:
DECLARE @mywhere VARCHAR(500) DECLARE @mystmt VARCHAR(1000) SET @mywhere = ' WHERE MfgPartNumber LIKE ''a%'' ' SELECT @mystmt = 'SELECT TOP 100 * FROM Products.Product AS p ' + @mywhere + ';' EXEC( @mystmt )
But I recommend instead that you do this:
SELECT TOP 100 * FROM Products.Product AS p WHERE ( MfgPartNumber LIKE 'a%' AND ModeMfrPartNumStartsWith=1) OR ( CategoryID = 123 AND ModeCategory=1 )
Stored procedure big where clause or multiple procedures , I would create One stored procedure with optional parameters. in where clause , for example even if @Var is null evaluates true sql server may still go ahead with the other Or condition and starts evaluating the whole column of params passed but a very bad execution plan for these very different set of� Passing datasource to a stored procedure: ctranjith: SQL Server 2000: 2: October 7th, 2004 01:49 PM: Passing a parameter value to Stored Procedure: mcinar: SQL Server 2000: 9: October 3rd, 2004 09:42 PM: passing stored procedure: shoakat: SQL Server 2000: 1: July 15th, 2004 09:20 AM: Stored Procedure - Dynamic Where Clause: Terry_Pino: BOOK
How to pass Where Clause as a parameter to the stored procedure , Hide Expand Copy Code. @AccountNumber As Varchar(50), @FirstName As Varchar(50), @LastName As Varchar(50), @ActiveClinicID As� Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure. D. Creating a procedure with input parameters. The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name.
Make sure you read this fully
SQL Server stored procedures for beginners, This article will provide a review of SQL Server stored procedures with we can just do it by simple ALTER PROCEDURE statement. only the procedure name is passed over the network instead of the whole T-SQL code. INSERT Stored Procedure in SQL Server Example 2. This example shows how to use the SELECT Statement and INSERT Statement inside the Stored procedure. From the below code snippet, you can see we are inserting all the records from Employee table into the EmployeeDup table using the INSERT INTO SELECT Statement
Dynamic SQL listed in some of the Answers is definitely a solution. However, if Dynamic SQL needs to be avoided, one of the solutions that I prefer is to make use of table variables (or temp tables) to store the parameter value that is used for comparison in WHERE clause.
Here is an example Stored Procedure implementation.
CREATE PROCEDURE [dbo].[myStoredProc] @parameter1 varchar(50) AS declare @myTempTableVar Table(param1 varchar(50)) insert into @myTempTableVar values(@parameter1) select * from MyTable where MyColumn in (select param1 from @myTempTableVar) GO
In case you want to pass in multiple values, then the comma separated values can be stored as rows in the table variable and used in the same way for comparison.
CREATE PROCEDURE [dbo].[myStoredProc] @parameter1 varchar(50) AS --Code Block to Convert Comma Seperated Parameter into Values of a Temporary Table Variable declare @myTempTableVar Table(param1 varchar(50)) declare @index int =0, @tempString varchar(10) if charindex(',',@parameter1) > 0 begin set @index = charindex(',',@parameter1) while @index > 0 begin set @tempString = SubString(@parameter1,1,@index-1) insert into @myTempTableVar values (@tempString) set @parameter1 = SubString(@parameter1,@index+1,len(@parameter1)-@index) set @index = charindex(',',@parameter1) end set @tempString = @parameter1 insert into @myTempTableVar values (@tempString) end else insert into @myTempTableVar values (@parameter1) select * from MyTable where MyColumn in (select param1 from @myTempTableVar) GO
Using Parameters for SQL Server Queries and Stored Procedures, In this tip we look at different ways to pass in values as parameters to queries and the Generally, when creating a condition in a query where you might use one of Parameterizing a Query By Making It a Stored Procedure separated by GO , and the SQLCMD variables are applied to the whole thing. Passing Where Clause Dynamically to Stored Procedure Idea is needed [Answered] RSS 10 replies Last post Jul 27, 2011 07:33 AM by D J
try this it works!!
CHARINDEX (',' + ColumnName + ',', ',' + REPLACE(@Parameter, ' ', '') + ',') > 0
execute syntax set @Parameter= 'nc1,nc2'
7 Using Procedures and Packages, If you test this block using SQL*Plus, then enter the statement SET You can pass PL/SQL tables as parameters to stored procedures and functions. persists for the lifetime of the session, this locks up UGA memory for the whole session. I have a T-SQL script, requirment is I need to call a stored procedure in where clause. This stored procedure accept a parameter and returns a bit result. Kindly guide me how to do it. Thanks. Edit: I cant modify this sp and make it a function. please
PL/SQL Language Elements, Use the OPEN-FOR statement when you need the flexibility to process a Instead, you can pass whole queries (not just parameters) to a cursor variable. Although a PL/SQL stored procedure or function can open a cursor variable and pass it� This is my problem, the stored procedure will search for items who have 0 as CityId for example, and for this, it return a wrong result. So it will be nice, to be able to have a dynamic where clause, based on if the value of int parameter is grater than 0, or not. Thanks in advance
Handling Data Returned from a SQL Stored Procedure, There are several ways to capture the output from a stored procedure in SQL way to pass any whole-number (integer) back from a stored procedure. In concluding a stored procedure with a SELECT statement, you open� In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. MySQL supports cursors inside stored programs.
How to pass a variable to a linked server query, When you have to pass in the whole Transact-SQL query or the name of the linked server (or both), Use the Sp_executesql Stored Procedure. Creating Stored Procedure-- Create Stored Procedure CREATE PROCEDURE TestParams @FirstParam VARCHAR(50), @SecondParam VARCHAR(50) AS SELECT @FirstParam FirstParam, @SecondParam SecondParam GO. Now let us see two different methods to call SP. Method 1: Let us run following two statements, where the order of the parameters is a different order.