Stored Procedure won't work - 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)

Stored Procedure won't work

Author

Topic

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 21:27:47

I created a Stored Procedure with a SELECT statement I know works. Here's the SP:

CREATE PROCEDURE [dbo].[QueryDesc]
(
@desc ntext
)
AS
if exists
(SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%')
begin
SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%'
END
if exists
(SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%')
begin
SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%'
END
else
begin
SELECT gr [Group], officeDesc Office, officeNum Extension
FROM dbo.tblOffice, dbo.tblGroup
WHERE
dbo.tblOffice.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%'
END
GO

When I run the SELECT statement by itself with a constant value, the SELECT statement works just fine. Why won't it work in the SP???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-28 : 21:41:44

You have single quotes around @desc. Should be:

LIKE '%' + @desc + '%'

Do you really need that first % sign? That's a performance issue. And also, ntext data type?

Tara

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 21:46:46

That did the trick. I knew it had to be something simple like that.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources