SQL query with a condtion

RIAS
RIAS used Ask the Experts™
on
Hello,
How to get an array  of  result and use the array in another query in a stored procedure.
Please find the scenerio in the attachment.

Regards,
Example1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Instead of

SET @SQL = @SQL + ' AND UserID =' + CAST(@UserID AS NVARCHAR(3)) + ' '

use

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

Note duplicate apostrophes around the XYZ. You'll probably replace ''XYZ''  by a variable.

Author

Commented:
Thanks will try and brb

Author

Commented:
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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

1) You may hardcode your Group name by replacing XYZ to whatever constant you need. Duplicate apostrophes are necessary because you are inserting apostrophe into a string enclosed in apostrophes.

2) Or you may pass the GroupName as a SP parameter and then the command could look like:

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

@GroupName must not contain apostrophes.

You could also look at sp_executeSQL (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017) which is better for SQL commands processing.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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) '

Author

Commented:
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.

Author

Commented:
Thanks

Author

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

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

Author

Commented:
Is the query syntax wrong?
Why do you think so?
Are you sure the variable values are not NULL?

Author

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

Author

Commented:
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

Author

Commented:
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 '

Open in new window

Author

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

Author

Commented:
It worked mate!!!

Author

Commented:
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 '

Open in new window

Author

Commented:
Scott thanks for your help!!
You are welcome

Author

Commented:
Hello pcelba,
Small problem

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial