IF EXISTS(EXEC @sql) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF EXISTS(EXEC @sql)

Author

Topic

vishalg
Starting Member

29 Posts

Posted - 2009-01-21 : 14:14:26

Hello guys. I am having a problem with this basic procedure in creating dynamic query. Particularly with the "IF EXISTS(EXEC @sql)" part. I looked around and it seems I cannot do this. Is there are way around this? I have given the simplest form of my procedure.

CREATE PROCEDURE [dbo].[spbCheck]
(
@PATIENTID INT
@SEX INT
@LOCATION INT
@ETHINICITY INT
)
AS
DECLARE @sql VARCHAR(500)
SET @sql='select PATIENT_NAME from paitentdb where PATIENTID=@PATIENTID '

IF(@SEX=0)
BEGIN
SET @sql=@sql+'AND PATIENT_SEX='MALE''
END

IF(@LOCATION=0)
BEGIN
SET @sql=@sql+' AND PATIENT_CITY='NEW YORK''
END

... i have a few more checking conditions like this

IF EXISTS(EXEC @sql)
BEGIN
PRINT
END

RETURN
GO

Thus basically I want to execute the @sql query in the "IF EXISTS".

Thanks

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-21 : 14:21:11

The IF EXISTS you are using doesn't follow the construct (I think).

Why not use case statements in your query instead?


SELECT PATIENT_NAME,PATIENTID=@PATIENTID,SEX=
       CASE PATIENT_SEX
         WHEN '0' THEN 'Male'
         WHEN '1' THEN 'Female'
         ELSE 'Not a Human'
       END,
       CITY=
       CASE PATIENT_CITY
         WHEN '0' THEN 'New York'
         WHEN '1' THEN 'Walla Walla'
         ELSE 'No City Found'
       END
FROM paitentdb
ORDERY BY PATIENT_NAME

something like that ?

r&r

vishalg
Starting Member

29 Posts

Posted - 2009-01-21 : 14:33:25

Thanks r&r but..

I cannot use CASE in my query because, the number of where clause conditions can be multiple to none. CASE will let me add only one or none conditions.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-21 : 14:46:20

quote:
Originally posted by vishalg

Thanks r&r but..

I cannot use CASE in my query because, the number of where clause conditions can be multiple to none. CASE will let me add only one or none conditions.

I am not sure what you mean by that. You can use multiple case statements to return the desired output for a given where condition.

I edited my post above to account for a couple of those conditions..

The only other way I can think of is to create a derived table composed of joins based on your WHERE conditions. This could get a bit messy though..

Maybe I do not understand your requirements?

r&r

vishalg
Starting Member

29 Posts

Posted - 2009-01-22 : 00:48:24

quote:

Originally posted by revdnrdy

The IF EXISTS you are using doesn't follow the construct (I think).

Why not use case statements in your query instead?


SELECT PATIENT_NAME,PATIENTID=@PATIENTID,SEX=
       CASE PATIENT_SEX
         WHEN '0' THEN 'Male'
         WHEN '1' THEN 'Female'
         ELSE 'Not a Human'
       END,
       CITY=
       CASE PATIENT_CITY
         WHEN '0' THEN 'New York'
         WHEN '1' THEN 'Walla Walla'
         ELSE 'No City Found'
       END
FROM paitentdb
ORDERY BY PATIENT_NAME

something like that ?

r&r

I used the "if (@sex=0)" condition in my code because if @sex=0, then sex is part of the where clause.. similarly if @location=0 then it is part of where clause. But in your code i have to take care of "sex" even though if it wont be required..

for example.. what is the final query i want to generate is

'select PATIENT_NAME from paitentdb where PATIENTID=@PATIENTID AND PATIENT_SEX='MALE'

thus in this case location does matter to me.. thus not a pat of my where clause. Also, just like sex and location there a lot more conditions to be checked like ethnicity, age, etc...

I hope I explained it well.. but u can surely ask again if not satisfied. THANKS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 01:32:15

No need for dynamic SQL here.
SELECT ...
FROM ...
WHERE PatientID = @PatientID
AND (Patient_Sex = @Sex OR @Sex IS NULL)
AND (Patient_City = @Location OR @Location IS NULL)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources