January 26, 2012 at 6:48 am
I am trying to get a single result using an if (else) statement in a stored procedure. I need to use multiple select statements inside the if statement but I get and error where the second select statement is telling me that it is expecting a ) to close my ( right after the if. Here is the original code that is returning 2 result sets, in 2 different rows. I need the code to return one single set of results in one single row.
code:
CREATE procedure [dbo].[nameofprocedure]
(@id as int = 1,
@pId as int = 0
)
--@patientName as varchar(100) = 'name'
as
begin
declare @lastGlds as int, @lastP as int, @txtPain3Other as varchar(max), @txtTargOther as varchar(max)
select @lastGlds = max(GldsID) from dbo.GLDS where patientID = @patientId
select @lastPsyDoc = MAX(id) from dbo.PsyDoc where PatientID = @patientId
if @patientId > -1
(
SELECT CAST( CASE WHEN numPhysInt >0 THEN 1 ELSE 0 END AS bit) as chkPhysical,
CAST( CASE WHEN numPillInt >0 THEN 1 ELSE 0 END AS bit) as chkStealing,
CAST( CASE WHEN numSleepInt >0 THEN 1 ELSE 0 END AS bit) as chkSleep
from dbo.GLDS where GldsID = @lastGlds
)
if @pId > -1
(
select cast(case when chkpain2HeadFace = 1 or chkpain2Neck = 1 or
chkpain2Neck = 1 or chkpain2Shoulder = 1 or chkpain2UpperExtremity = 1
or chkpain2Hands = 1 or chkpain2Thoracic = 1 or chkpain2Chest = 1
or chkpain2Sternum = 1 or chkpain2Ribs = 1 or chkpain2LowBack = 1 or
chkpain2Hip = 1 or chkpain2Buttocks = 1 or chkpain2Abdomen = 1 or
chkpain2Genital = 1 or chkpain2Knee = 1 or chkpain2LowerExtremity = 1
or chkpain2Feet = 1 or txtPainSiteOther <> '' then 1 else 0 end as bit) as chkPain,
CAST(case when chkpain3Disfigurement = 1 then 1 else 0 end as bit) as
chkDisfigurement,
CAST(case when chkpain3Dysphagia = 1 then 1 else 0 end as bit) as chkDysphagia
from dbo.P where id = @lastP
)
else
(
select ISNULL(f.[name],'') as [F],
isnull(T.[sT], 0) as [sT],
ISNULL(T.[isF], 0) as [isF]
from P.dbo.T T
left join dbo.[p] patient on p.id = T.pID
left join dbo.[f] f on p.[fcode] = f.
left join dbo. behavioralCognitive on B.[id] = T.BID
where T.[id] = @id
)
end
GO
January 26, 2012 at 7:08 am
I don't think that is correct syntax.
Store the two statements in variables. Then execute the statement in the IF construct with the EXEC statement or with sp_executesql.
edit: sorry, I wasn't really awake. Nesting SQL statements in an IF construct is still valid syntax.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 26, 2012 at 7:25 am
The way you have the procedure written can logically return 2 data sets as you are checking for different conditions @patientid > -1 and @pid > -1
Does your application always pass in values for both parameters? The procedure you have given us has values already assigned to the parameters which will make both IF statements evaluate to true therefore return 2 result sets.
January 26, 2012 at 7:36 am
the parameters that are passed are @plid and @id. when @plid is 0(the first if > -1 statement, which I need to combine with the second if > -1 statement) I need to do a select case on two tables, thus obtaining results from 2 different tables. But, I want to combine the results into a single row so that I can use it in my C# code using a SQLdatareader. When I put the select case statements in the first if > -1 statement and get rid of the second if > -1 statement and use an or in between the from table statement at the end I get the same 2 result sets. Thanks for your responses.
January 26, 2012 at 7:50 am
You won't be able to combine the results from both statements as the columns are not the same. If the columns were the same in each statement you could union them together and create one result set.
January 26, 2012 at 8:06 am
Thanks, do you know how to retrieve multiple result sets in C#?
thanks in advance.
January 26, 2012 at 8:11 am
Loundy (1/26/2012)
You won't be able to combine the results from both statements as the columns are not the same. If the columns were the same in each statement you could union them together and create one result set.
Loundy is correct. Your first two queries have 3 bit fields, but the third query is a bit vague. We can however tell that the first column is a varchar, columns 2 and 3 may or may not be bits.
I know the following will not quite work because of datatype differences but something similar should work.
SELECT CAST( CASE WHEN numPhysInt >0 THEN 1 ELSE 0 END AS bit) as chkPhysical,
CAST( CASE WHEN numPillInt >0 THEN 1 ELSE 0 END AS bit) as chkStealing,
CAST( CASE WHEN numSleepInt >0 THEN 1 ELSE 0 END AS bit) as chkSleep
from dbo.GLDS where GldsID = @lastGlds and @patientId > -1
union all
select cast(case when chkpain2HeadFace = 1 or chkpain2Neck = 1 or
chkpain2Neck = 1 or chkpain2Shoulder = 1 or chkpain2UpperExtremity = 1
or chkpain2Hands = 1 or chkpain2Thoracic = 1 or chkpain2Chest = 1
or chkpain2Sternum = 1 or chkpain2Ribs = 1 or chkpain2LowBack = 1 or
chkpain2Hip = 1 or chkpain2Buttocks = 1 or chkpain2Abdomen = 1 or
chkpain2Genital = 1 or chkpain2Knee = 1 or chkpain2LowerExtremity = 1
or chkpain2Feet = 1 or txtPainSiteOther <> '' then 1 else 0 end as bit) as chkPain,
CAST(case when chkpain3Disfigurement = 1 then 1 else 0 end as bit) as chkDisfigurement,
CAST(case when chkpain3Dysphagia = 1 then 1 else 0 end as bit) as chkDysphagia
from dbo.P where id = @lastP
and @pId > -1
union all
select ISNULL(f.[name],'') as [F],
isnull(T.[sT], 0) as [sT],
ISNULL(T.[isF], 0) as [isF]
from P.dbo.T T
left join dbo.[p] patient on p.id = T.pID
left join dbo.[f] f on p.[fcode] = f.
left join dbo. behavioralCognitive on B.[id] = T.BID
where T.[id] = @id
and @pId <= -1
Another option might be to use a temp table and insert into that temp table and simply select everything from it at the end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 26, 2012 at 8:21 am
Loundy (1/26/2012)
You won't be able to combine the results from both statements as the columns are not the same. If the columns were the same in each statement you could union them together and create one result set.
With my limited knowledge of C# using ORMs (entity framework) i don't think its possible. you "could" split the procedure into 2 and then read both procedures into your code?
January 26, 2012 at 8:45 am
Thanks to all that replied. Forget the last question, it's trivial now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply