Solved

SQL Server SP parameter problem

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 40
VBScript Write Column Headers 3 32
T-SQL: "HAVING CASE" Clause 1 23
insert wont work in SQL 14 18
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

10 Experts available now in Live!

Get 1:1 Help Now