SQL query with a condtion

Thanks will try and brb

Can you please clarify with example
Note duplicate apostrophes around the XYZ. You'll probably replace ''XYZ''  by a variable.
I am a bit confused on what to do about this.

If the values are in a table, leave them there.  Just use the table directly.  A bit confusing since "userID from tableUsers" doesn't exist (convenient here, actually, so the "UserID" column doesn't need a table alias), I'm assuming you mean "ID".

SET @SQL = @SQL + ' AND EXISTS(SELECT 1 FROM dbo.TableUsers TU WHERE TU.UserGroupName = ''XYZ'' AND TU.ID = UserID) '

Thanks Scott,
Why are you selecting only 1

(SELECT 1 FROM dbo.TableUser
I need to get an array of userID which have groupname ='XYZ'

The difference is in  EXISTS  and   IN   operator. Scott's variant contains UserID in WHERE clause.

Thanks

I don't know why the Stored Procedure is not picking this if condition:

IF NOT (@IsPowerUser=1 OR @IsAdmin=1)

Is the query syntax wrong?

Why do you think so?
Are you sure the variable values are not NULL?

Yes, I have set them as false .
When I remove this IF condition it works fine .

Please refer to my SP in the example attached.

The example is rather incomplete… but you may try to add following commands below the Query:

IF @IsAdmin IS NULL
  SET @IsAdmin = 0
IF @IsPowerUser IS NULL
  SET @IsPowerUser = 0

ALTER PROCEDURE [dbo].[RecentJobs_St] (

  @SelectedDateTime			DATETIME
, @AccountName				NVARCHAR(200)
)
AS
BEGIN

SET NOCOUNT ON 

DECLARE @SQL	NVARCHAR(2000)
DECLARE @IsPowerUser		BIT
DECLARE @IsAdmin			BIT
DECLARE @UserID				INT

-- This query gets a UserID if the user is a power user or an admin
-- If they are then they can see all records, otherwise just their own.

SELECT @UserID = ID
, @IsAdmin = IsAdmin
, @IsPowerUser = IsPowerUser
FROM Users U
WHERE U.AccountName = @AccountName

SET @SQL = 'SELECT TOP 50 * 
FROM JOB_Recnt R
WHERE CONVERT(NVARCHAR(20),R.[Date Time],112) <= ' + CONVERT(NVARCHAR(20),@SelectedDateTime,112) + ' '

IF NOT (@IsPowerUser=1 OR @IsAdmin=1)

SET @SQL = @SQL + ' AND UserID IN (SELECT ID FROM USERS WHERE UserGroupName = ''XYZ'')'

SET @SQL = @SQL + ' ORDER BY CAST([Date Time] AS datetime) DESC '

Select allOpen in new window

Here you go with the complete SP.
Can you suggest where can I add the IF condition please

It worked mate!!!

Thanks!

ALTER PROCEDURE [dbo].[RecentJobs_St] (

  @SelectedDateTime			DATETIME
, @AccountName				NVARCHAR(200)
)
AS
BEGIN

SET NOCOUNT ON 

DECLARE @SQL	NVARCHAR(2000)
DECLARE @IsPowerUser		BIT
DECLARE @IsAdmin			BIT
DECLARE @UserID				INT

-- This query gets a UserID if the user is a power user or an admin
-- If they are then they can see all records, otherwise just their own.

SELECT @UserID = ID
, @IsAdmin = IsAdmin
, @IsPowerUser = IsPowerUser
FROM Users U
WHERE U.AccountName = @AccountName

IF @IsAdmin IS NULL
  SET @IsAdmin = 0
IF @IsPowerUser IS NULL
  SET @IsPowerUser = 0 

SET @SQL = 'SELECT TOP 50 * 
FROM JOB_Recnt R
WHERE CONVERT(NVARCHAR(20),R.[Date Time],112) <= ' + CONVERT(NVARCHAR(20),@SelectedDateTime,112) + ' '

IF NOT (@IsPowerUser=1 OR @IsAdmin=1)
  SET @SQL = @SQL + ' AND UserID IN (SELECT ID FROM USERS WHERE UserGroupName = ''XYZ'')'

SET @SQL = @SQL + ' ORDER BY CAST([Date Time] AS datetime) DESC '

Select allOpen in new window

Scott thanks for your help!!

You are welcome

Hello pcelba,
Small problem

Now I have made the IsAdmin and IsPoweruser = True but still the query is picking up the if condition

The best you can do here is to add PRINT commands inside your SP and call the SP from SSMS.
You should print the @UserID after the first query
You should print the final SQL command
You may print the values of @IsAdmin and @IsPowerUser

SQL Server does not know True or False. Bit values are  0, 1, or possibly NULL which means undefined.