CODEWITHSUNDEEP
sqlsql-serversql-server-2012
indofraiser
indofraiser

Reputation: 1024

Invalid variable

This stored procedure works until I add in a variable. The @sWHERE variable will be populated from a a fixed input.

I'm not sure what I need to amend ...I've tried adding & and +

Error: 4145, Level 15, State 1, Procedure spUniqueUPRN, Line 12 An expression of non-boolean type specified in a context where a condition is expected, near 'Group'.

Stored Procedure:

USE [DB]
GO 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
ALTER PROCEDURE [dbo].[spUniqueUPRN]    
@sWHERE varchar(Max) 
AS
BEGIN   
SET NOCOUNT ON;   
SELECT  DISTINCT UPRN INTO TBLTEMP FROM TblA
WHERE  @sWHERE
Group BY UPRN
UNION ALL
SELECT  UPRN FROM TblP
Group BY UPRN 
END

VERSION 2 based on help so far (no where near final just a step)

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUniqueUPRN]    
@sWHERE varchar(Max) 
AS
BEGIN   
SET NOCOUNT ON; 
---------- 
DROP TABLE TBLTEMP

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'UPRN INTO TBLTEMP FROM TblA ' + QUOTENAME(@sWHERE) + '
Group BY UPRN
UNION ALL
SELECT  UPRN FROM TblP
Group BY UPRN 
';
EXEC sp_executesql @SQL;
---------- 
END

Verion 3

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[spUniqueUPRN]    
@sDateFrom Date,
@sDateTo Date,
@sUPRN varchar(Max),
@sRiskRating varchar(10),
@sSurveyCompany varchar(Max),
@sPostcode varchar(10),
@sStreet varchar(Max),
@sRegion Int

AS
BEGIN   
SET NOCOUNT ON; 
---------- 
DROP TABLE TBLTEMP

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = 'SELECT UPRN INTO TBLTEMP FROM TblA WHERE 1 = 1 ' 
IF QUOTENAME(@sDateFrom) IS NOT NULL AND QUOTENAME(@sDateTo) IS NOT NULL
    SELECT @SQL = @SQL + ' or SurveyDate BETWEEN ' +  QUOTENAME(@sDateFrom) + ' AND ' + QUOTENAME(@sDateTo) + ''''

IF QUOTENAME(@sRiskRating) IS NOT NULL 
  SELECT @SQL = @SQL + ' or OverAllRiskCategory = ' + QUOTENAME(@sRiskRating)    +''''

IF QUOTENAME(@sUPRN) IS NOT NULL 
  SELECT @SQL = @SQL + ' or UPRN LIKE ' + QUOTENAME(@sUPRN) + ''''

IF QUOTENAME(@sSurveyCompany) IS NOT NULL 
  SELECT @SQL = @SQL + ' or SurveyCompany LIKE ''%' +  QUOTENAME(@sSurveyCompany)  + '%'''

SELECT @SQL = @SQL + ' GROUP BY UPRN '
SELECT @SQL = @SQL + ' UNION ALL '


SELECT  UPRN FROM TblProperty
 IF QUOTENAME(@sPostcode) IS NOT NULL 
  SELECT @SQL = @SQL + ' or Postcode  LIKE ''%' +   QUOTENAME(@sPostcode)  + '%'''

IF QUOTENAME(@sStreet) IS NOT NULL 
  SELECT @SQL = @SQL + ' or Street  LIKE ''%' +  QUOTENAME(@sStreet)  + '%'''

IF QUOTENAME(@sRegion) IS NOT NULL 
  SELECT @SQL = @SQL + 'or Region  LIKE ''%' +  QUOTENAME(@sRegion)  + '%'''

SELECT @SQL = @SQL + 'GROUP BY UPRN'

EXEC sp_executesql @SQL;
---------- 
END

Upvotes: 0

Views: 143

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

First of all to pass @sWHERE as string to stored procedure you need to use dynamic SQL.

Before you use dynamic SQL I strongly recommend to read The Curse and Blessings of Dynamic SQL by Erland Sommarskog.

Your case is SELECT * FROM tbl WHERE @condition:

If you are considering to write the procedure

CREATE PROCEDURE search_sp @condition varchar(8000) AS 
SELECT * FROM tbl WHERE @condition

Just forget it. If you are doing this, you have not completed the transition to use stored procedure and you are still assembling your SQL code in the client. But this example lapses into Dynamic Search Conditions/Dynamic Crosstab.

The point is that you could use (as proposed in comments)

DECLARE @sql NVARCHAR(MAX) = 
N'SELECT *
  FROM table_name
  WHERE ' + @sWHERE;

EXEC dbo.sp_executesql
       @sql;

But this is straight way to SQL Injection attacks. User may call:

EXEC dbo.spUniqueUPRN '1=1; DROP TABLE ....; --'

To sum up, it is up to you if you will use WHERE @condition. It is possible but I would not go this path.

Upvotes: 1

Related Questions