CODEWITHSUNDEEP
sql-server
BrettFK
BrettFK

Reputation: 39

SQL Join where row exists in single table

I suspect this has been answered before however I can't seem to find a post that matches what I am trying to accomplish.

I have the following stored procedure. This procedure is only producing results based on the first IN clause and not the second.

USE [asl]
GO
/****** Object:  StoredProcedure [dbo].[aslGetCommonv1]    Script Date: 10/04/2020 3:46:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[aslGetCommonv1]
    @StartDate varchar(10),
    @EndDate varchar(10),
    @GroupBy varchar(100),
    @Select varchar(100)
AS
    DECLARE @SQL VARCHAR(MAX) 
    SET NOCOUNT ON;
    SET @SQL = @Select + '
    COUNT(*) AS total
    FROM dbo.requests_services rqs
    LEFT JOIN services      svc ON svc.service_id = rqs.service_id
    LEFT JOIN requests         rq ON rq.request_id = rqs.request_id
    LEFT JOIN doctors          do ON do.doctor_id  = rq.doctor_id
    WHERE rqs.service_id IN (1005, 1006, 1007, 1008, 1401, 1402, 1404, 1405, 1407, 1408, 1410, 1501, 1503, 1504, 1505, 1506, 1509, 5101, 5103) OR
    svc.service_abbrev IN (SADREN, SADREN2, SFH28, SLPSAL, TFT, CTA, VITD, U216 OH, U2416 OH, UEMET, CALP, FAEPCR, CDSA1, CDSA2, CDSA3, CDSA3+, CDSA4, CDSA4+, CDSA5, INTPERM, SIBO2, SIBO3, GIEFFCO, GIEFFME, CMAP,
    CMMR, IGA96, IGA96AS, IGG144, IGA144, IGG208, IGA208, IGG+A208, ALC050, ALC100, ALC150, ALC200, ALCPLAT, CYTOK, HIAM, DAO, KRPR, ENEUM, LIPOSCRN, INEUM, ANEUM, ORGANIX, UIODL, MTHFR, OM3INPR,
    ONCOSTAT, ONCONEXT, ONCONPLUS, ONCOTRACE, ONCOTRAIL, ONCOCOUNT, IMMUNOSTAT, NAGALASE, PRIMESPOT, CHEMOSNIP) AND rq.entry_date BETWEEN ' + @StartDate + ' AND ' + @EndDate + '
    GROUP BY ' + @GroupBy + '
    ORDER BY rqs.service_id ASC'

    EXEC(@SQL)

(Please ignore the fact the dates are varchar). Now, I believe that the reason for blank results from the second IN clause is that the table dbo.services is not referenced in the SELECT or JOIN statements - I just don't know to put it in there in the proper manner.

The "service_abbrev" column only exists in a single table (dbo.services). The only other column in this table is the "service_id" field, which as above is joined from dbo.requests_services column service_id.

So, what is the best way to get the table dbo.servcices referenced in the procedure so that results are returned from both IN clauses?

** EDIT ** So, I need to change the code to look like this for the date? BETWEEN ' + '@StartDate' + ' AND ' + '@EndDate' +

The strings SADREN, SADREN2, etc, are only accepted if I double quote them - could this be because I've not quoted the @Dates as above?

Also, here is the query I am using to test:

USE asl;

DECLARE @StartDate varchar(10)
SET @StartDate = '2018-02-01'

DECLARE @EndDate varchar(10)
SET @EndDate = '2019-06-20'

DECLARE @GroupBy VARCHAR(100)
SET @GroupBy = 'rqs.service_id,svc.service_abbrev'

DECLARE @Select VARCHAR(100)
SET @Select = 'SELECT rqs.service_id,svc.service_abbrev,'

EXEC dbo.aslGetCommonv1 @StartDate, @EndDate, @GroupBy, @Select

Upvotes: 0

Views: 63

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

There a are a number of things wrong.

  1. SADREN, SADREN2, SFH28 etc are strings and should be single quoted

  2. BETWEEN ' + @StartDate + ' AND ' + @EndDate + - the dates need to be quoted

  3. you have a group by but the group by column is not included in the select clause

  4. the order by column is not included in the select so is invalid.

Other than that the query works. You can debug this by generating the sql statement, selecting it having change the query option in ssms to text and then running it (without executing).

Upvotes: 1

Related Questions