SQL Server SP parameter problem

I am trying to create an SP as follows:-

ALTER PROCEDURE [dbo].[VhcGetCurrentJobs] @Branch Int, @jobId  Int, @JobDate  DateTime, @Function char(1)
AS
DECLARE @QString VarChar(2000)
DECLARE @WString VarChar(200)
if @JobId > 0
      begin
            if @Function = '>'
                  set @WString = ' WHERE JobsId > ' & @JobId & ' AND BljBlctlId = ' & @Branch
            else
                  set @WString = ' WHERE JobsId = ' & @JobId  
      end
else
      begin
            if @Function = '>'
                  set @WString = ' WHERE Date > convert(datetime,  ''' + @JobDate + ''', 103)'
            else
                  set @WString = ' WHERE Date = convert(datetime,  ''' + @JobDate + ''', 103)'
      end  
      
      
 set @QString = 'SELECT  BlJobs.JobsId AS JobId
 FROM         BlJobs INNER JOIN
                      Slmas ON BlJobs.BljSlmasId = Slmas.SlmasId INNER JOIN
                      Admas ON Slmas.AdmasId = Admas.AdmasId' + @WString      

I am trying to build the WHERE clause in the SP based on parameters passed to it.

While the above does complile, when I try to call it with any value for @JobId I get an error

CONVERSION FAILED WHEN CONVERTING VARCHAR VALUE 'Where JobsId = ' TO DATA TYPE INT.

Any ideas??
grwallaceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Surendra NathConnect With a Mentor Technology LeadCommented:
try this
ALTER PROCEDURE [dbo].[VhcGetCurrentJobs] @Branch Int, @jobId  Int, @JobDate  DateTime, @Function char(1)
AS
BEGIN

DECLARE @QString VarChar(2000)
DECLARE @WString VarChar(200)
if @JobId > 0 
      begin
            if @Function = '>' 
                  set @WString = ' WHERE JobsId > ' + CAST(@JobId AS varchar) & ' AND BljBlctlId = ' & CAST(@Branch AS varchar)
            else
                  set @WString = ' WHERE JobsId = ' + CAST(@JobId AS varchar)
      end 
else
      begin
            if @Function = '>' 
                  set @WString = ' WHERE Date > convert(datetime,  ''' + convert(varchar(50),@JobDate,103) + ''', 103)'
            else
                  set @WString = ' WHERE Date = convert(datetime,  ''' + convert(varchar(50),@JobDate,103) + ''', 103)'
      end  
      
      
 set @QString = 'SELECT  BlJobs.JobsId AS JobId
 FROM         BlJobs INNER JOIN
                      Slmas ON BlJobs.BljSlmasId = Slmas.SlmasId INNER JOIN
                      Admas ON Slmas.AdmasId = Admas.AdmasId' + @WString   
					  
exec @QString
END

Open in new window

0
 
Anoo S PillaiCommented:
Check the values in column JobsId. The chances are that there are values in this column which are not convertible to data type INT.

This can be easily verified with a SELECT like the following

select *
from BlJobs
where ISNUMERIC ( JobsId ) <> 1
0
 
grwallaceAuthor Commented:
Almost perfect neo_jarvis.

I took you solution and changed the Exec line to:-

exec(@Qstring) and it worked perfectly

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.