Prolem with Store SP with variables and parameters to exec a select

[Gonfer]

Hi guys

I'm trying to execute a store procedure in the query designer of a Dataset report in SSRS in visual studio. Depending of what I choose in the parameter called p_member (With membership, or No membership the Sp must to execute the select with the value text in the conditional, but I have a mistake when I try to compile teh SP.


CREATE PROCEDURE FILTER2 @P_MEMBER VARCHAR(20), @P_ACCOUNTS VARCHAR(20)
AS
DECLARE
@sqlstr char(200),
@strmember varchar(50)

IF @P_MEMBER ='No members'
Set @strmember = '(Qmemberships IS NULL or Qmemberships = 0)'
ELSE
Set @strmember = '(Qmemberships IS NOT NULL or Qmemberships > 0)'


SELECT DISTINCT gno_guests, GuestLName, GuestFNAme, GuestDate, QMemberships, Qreserv,
QAccounts, QItems, GuestEmail, GuestAddress, GuestCity, GuestZip, GuestCountry, GuestCode, GuestPhone FROM CONSOSIRIUS WHERE @strmember


Msg 4145, Level 15, State 1, Procedure FILTER2, Line 15
An expression of non-boolean type specified in a context where a condition is expected, near '@strmember'.

I will apprecite any suggestion please, thanks in advance

GonFer

 

[bborissov]

You can't use variable as WHERE clause.
Better run two queries based on that parameter

CREATE PROCEDURE FILTER2 @P_MEMBER VARCHAR(20), @P_ACCOUNTS VARCHAR(20)
AS
DECLARE  @sqlstr char(200)

IF @P_MEMBER ='No members'
  SELECT DISTINCT gno_guests, GuestLName, GuestFNAme, GuestDate, QMemberships, Qreserv,
                  QAccounts, QItems, GuestEmail, GuestAddress, GuestCity, GuestZip, GuestCountry,
                  GuestCode, GuestPhone
  FROM CONSOSIRIUS
  WHERE ISNULL(Qmemberships, 0) = 0
ELSE
  SELECT DISTINCT gno_guests, GuestLName, GuestFNAme, GuestDate, QMemberships, Qreserv,
                  QAccounts, QItems, GuestEmail, GuestAddress, GuestCity, GuestZip, GuestCountry,
                  GuestCode, GuestPhone
  FROM CONSOSIRIUS
  WHERE ISNULL(Qmemberships, 0) > 0

Borislav Borissov
VFP9 SP2, SQL Server

 

[flapeyre]

Or, you can use dynamic SQL:

CREATE PROCEDURE FILTER2
	@P_Member VARCHAR(20)
,	@P_Accounts VARCHAR(20)
	AS
		DECLARE	@Sql NVARCHAR(MAX)
			,	@StrMember VARCHAR(50)

		IF @P_Member = 'No members'
			SET @StrMember = [COLOR=#EF2929]N'(Qmemberships IS NULL or Qmemberships = 0)'[/color]
		ELSE
			SET @StrMember = [COLOR=#EF2929]N'(Qmemberships IS NOT NULL or Qmemberships > 0)'[/color]

		SET @Sql =
	[COLOR=#EF2929]	N'SELECT DISTINCT gno_guests
	, GuestLName
	, GuestFNAme
	, GuestDate
	, QMemberships
	, Qreserv
	, QAccounts
	, QItems
	, GuestEmail
	, GuestAddress
	, GuestCity
	, GuestZip
	, GuestCountry
	, GuestCode
	, GuestPhone
FROM CONSOSIRIUS WHERE'[/color]

		+ @StrMember

		EXEC @Sql
GO

-- Francis
Aut viam inveniam aut faciam.