Solved

SQL Server SP parameter problem

Posted on 2013-05-26
3
275 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 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now