asked on

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??

Anoo S Pillai

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

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

grwallace

ASKER

Almost perfect neo_jarvis.

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

exec(@Qstring) and it worked perfectly

Thanks!