Help with Stored Procedure - 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 2008 Forums

Transact-SQL (2008)

Help with Stored Procedure

Author

Topic

Darkmatter5
Starting Member

17 Posts

Posted - 2012-09-06 : 13:28:21

Here's my stored procedure
[CODE]
ALTER PROCEDURE [dbo].[sp_GetLikeJobsByJobNumber]
-- Add the parameters for the stored procedure here
@number varchar
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT jobs.JobID,
clients.ClientIndividualID,
clients.ClientCompanyID,
employees.EmployeeID,
clientIndividuals.IndividualFirst_name,
clientIndividuals.IndividualLast_name,
clientCompanies.CompanyName,
employees.EmployeeFirst_name,
employees.EmployeeLast_name,
jobs.JobNumber,
jobs.JobDescription AS Description,
jobs.JobLocation AS Location,
clients.ClientFull_name As Client,
jobs.JobMemo_info As Memo,
CAST(areas.Area AS VARCHAR(9)) + units.UnitName AS JobArea,
employees.EmployeeLast_name + ', ' + employees.EmployeeFirst_name AS Employee
FROM jobs
INNER JOIN jobs2clients ON jobs.JobID = jobs2clients.JobID
INNER JOIN clients ON jobs2clients.ClientID = clients.ClientID
INNER JOIN clientIndividuals ON clients.ClientIndividualID = clientIndividuals.ClientIndividualID
INNER JOIN clientCompanies ON clients.ClientCompanyID = clientCompanies.ClientCompanyID
INNER JOIN employees ON jobs.EmployeeID = employees.EmployeeID
LEFT OUTER JOIN areas ON jobs.JobID = areas.JobID
LEFT OUTER JOIN units ON areas.UnitID = units.UnitID
WHERE jobs.JobNumber LIKE '%' + @number + '%'
ORDER BY jobs.JobNumber
END[/CODE]

This runs correctly as a straight query, but not as a stored procedure. It's like it's returning almost everything in the database. I think it's with the "'%' + @number + '%'" section. I've tried escaping the single quotes with "'''%' + @number + '%'''", but that returns nothing.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-06 : 13:39:06

You'll need to explain in better detail the issue. You are changing something if it's returning something different as a stored procedure. Show us the query that works correctly and then show us the call to the stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog