Solved

SQL Server SP parameter problem

Posted on 2013-05-26
3
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

635 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