?
Solved

SQL Server SP parameter problem

Posted on 2013-05-26
3
Medium Priority
?
289 Views
Last Modified: 2013-05-26
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??
0
Comment
Question by:grwallace
3 Comments
 
LVL 7

Expert Comment

by:Anoo S Pillai
ID: 39197514
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
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39197529
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
 

Author Closing Comment

by:grwallace
ID: 39197557
Almost perfect neo_jarvis.

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

exec(@Qstring) and it worked perfectly

Thanks!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question