What's wrong with this SP? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

What's wrong with this SP?

Author

Topic

Valentin-
Starting Member

7 Posts

Posted - 2003-05-10 : 12:40:18

CREATE PROCEDURE adm_showcontact
(
@DomainID INT
)
AS
DECLARE @Registrant INT
DECLARE @Admin INT
DECLARE @Tech INT

IF EXISTS

(SELECT @Registrant = registrant, @Admin = admin, @Tech = tech, domain_id, domain_name, domain_type, product_type,
billing FROM domains WHERE domain_id = @DomainID AND state = 0)

THEN BEGIN

SELECT reg_name, org, adress1, adress2, adress3, city, province, postalcode, telephone, fax, email
FROM contacts WHERE contact_id = @Registrant OR contact_id = @Admin OR contact_id = @Tech

END

ErrorCode:
Server: Msg 170, Level 15, State 1, Procedure adm_showcontact, Line 12
Line 12: Incorrect syntax near '='.

Could anyone help me?
Thanks a lot.

Bye

hemchand
Starting Member

2 Posts

Posted - 2003-05-10 : 14:19:38

An assignment to a variable is not possible in an expression which can return only true or false. Try the assignent within the 'IF' block. Also the 'Then' you have used is not required.

Bye

Try this:

CREATE PROCEDURE adm_showcontact
(
@DomainID INT
)
AS

IF EXISTS (SELECT * FROM domains WHERE domain_id = @DomainID AND state = 0)

BEGIN

SELECT reg_name, org, adress1, adress2, adress3, city, province, postalcode, telephone, fax, email
FROM contacts AS a INNER JOIN domains AS b ON b.domain_id = @DomainID AND b.state = 0 AND (a.contact_id = registrant OR a.contact_id = admin OR a.contact_id = tech)

END

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-10 : 16:12:38

There may be a very good reason, but why bother with the test at all? If it doesn't exist, an empty recordset is the worst possible problem.

Try this:

CREATE PROCEDURE adm_showcontact
(
@DomainID INT
)
AS

SELECT reg_name, org, adress1, adress2, adress3, city, province, postalcode, telephone, fax, email

FROM contacts AS a INNER JOIN domains AS b

ON b.domain_id = @DomainID AND b.state = 0 AND (a.contact_id = registrant OR a.contact_id = admin OR a.contact_id = tech)

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources