June 11, 2010 at 12:45 pm
CREATE PROCEDURE SP_TestReqSearch
@patID nvarchar(16),
@patName text,
@CaseNo nvarchar(12),
@testID nvarchar(8),
@dt date
AS
BEGIN
SET NOCOUNT ON;
IF (@patID <> '0' AND @patName='0' AND @CaseNo='0' AND @testID='0' AND @dt='0') THEN
SELECT s.pat_id AS [PatientID],r.pat_name AS [PatientName],r.case_no AS [CaseNo],s.test_id AS [test_id],s.dt AS [Date]
FROM Pathology.dbo.SchedListTable s
INNER JOIN Pathology.dbo.RegTable r ON s.pat_id=r.pat_id
INNER JOIN Pathology.dbo.RegTable r ON s.dt=r.dt
WHERE s.pat_id=@patID
ORDER BY dt ASC
END IF
END
GO
June 11, 2010 at 4:09 pm
I'm not sure what an error 156 is, but you've got at least three syntax errors:
In TSQL, the syntax for IF...ELSE is:
IF expression
BEGIN
...
END
ELSE
BEGIN
...
END
There is no then (also there is no End If, just IF...BEGIN ... END)
Also, when you have more than one condition in a join, use AND
i.e.
SELECT *
FROM MyTable T
INNER JOIN MyOtherTable O
ON T.id = O.ID
AND T.id2 = O.ID2
Also, there is no data type of Date, you may want to use datetime.
Finally, the 'text' data type is depricated, use VARCHAR(MAX)
I hope this helps.
SQL guy and Houston Magician
June 13, 2010 at 5:39 pm
I bet returned error was something like... Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156) ... isn't it?
Next time please copy/paste full error message so to get help faster.
Please follow Robert advise.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 14, 2010 at 5:46 am
U sure its error 156?or is it msg 156???
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply