Passing the complete SQL stored procedure from the WHERE clause

Question

I have a stored form SQL procedure

SELECT [fields] FROM [table] WHERE @whereSql

I want to pass an argument (@whereSql) to the procedure that defines 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?

Answer 1

The short answer is that you cannot do it this way: SQL Server treats the contents of a variable as VALUE. It does not dynamically increment the line to execute (which is why this is the right way to avoid SQL injection attacks).

, , , , . WHERE, OR, .

, , EXEC it.

, :

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 )

:

SELECT TOP 100 * 
    FROM Products.Product AS p 
    WHERE 
        ( MfgPartNumber LIKE 'a%' AND ModeMfrPartNumStartsWith=1)
    OR  ( CategoryID = 123 AND ModeCategory=1 )

Answer 2

, Dynamic SQL. . :

CREATE PROCEDURE [dbo].[myProc]
@whereSql nvarchar(256)

AS
    EXEC('SELECT [fields] FROM [table] WHERE ' + @whereSql)
GO

, SQL, . , :

• http://www.sommarskog.se/dynamic_sql.html

• http://msdn.microsoft.com/en-us/library/aa224806%28SQL.80%29.aspx

• http://www.itjungle.com/fhg/fhg100505-story02.html

Answer 3

,

www.sommarskog.se/dynamic_sql.html

Answer 4

http://sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure

, !!

CHARINDEX (',' + ColumnName + ',', ',' +
REPLACE(@Parameter, ' ', '') + ',') > 0

set @Parameter = 'nc1, nc2'

Answer 5

SQL, , . , Dynamic SQL, , , ( ) , WHERE.

.

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

If you want to pass multiple values, the values ​​separated by commas can be saved as strings in a 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