Error in stored procedure

When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near'@.SQL'.

Here is my procedure. Could someone tell me what mistake I'm doing.

Alterprocedure [dbo].[USP_SearchUsersCustomers_New]

@.UserIDINT

,@.RepName VARCHAR(50)

,@.dlStatus VARCHAR(5)=''

as

Declare

@.Criteria VARCHAR(500)

,@.SQL VARCHAR(8000)

SELECT @.Criteria=''

SET NOCOUNTON

if(@.dlStatus<>'ALL'AND(LEN(@.dlStatus)>1))

BEGIN

if(@.dlStatus='ALA')

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=1'

else

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=0'

END

--If the user is an Admin, select from all users.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), 'Unassigned') as 'RoleName'

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

In your dynamic sql string, you need to escape the single quote by using it twice, i.e.: 'RoleName' should be ''RoleName''.

Also, before you build this string. make sure you test out the actual query first.

|||

I tried it still I get the same error "Incorrect Syntax near @.SQL". The query works fine when I execute it alone.

Here is the code again.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), ''Unassigned'') as ''RoleName''

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

|||

SET @.SQL = '-- code here'

|||

I did that. Now when I click compile and execute it doesn't show any error. But when I execute the stored procedure it shows an error "Must declare @.UserID". But it's already declared.

|||

You dont need to use dynamic SQL in your scenario. Try something like this with your regular SELECT statement:

ANDdbo.tbl_Security_Users.IsActive= (CASEWHEN@.dlStatus='ALL'THENdbo.tbl_Security_Users.IsActive

WHEN@.dlStatus='ALA'Then1ELSE0END )

|||

Thanks! It worked and I liked the simplicity of the code while achieving the desired task.