Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: Dynamic Built Where Clause - Error in Stored Procedure

    I'm sending a stored procedure a number of parameters, including an int for a foreign key field. I'm trying to dynamically build the Where Clause of the query.

    I'm getting the error:

    Syntax error converting the varchar value 'wo_dutfk = ' to a column of data type int.

    NOTE: wo_dutfk is an int field within the WO table.

    Here's the code:

    CREATE PROCEDURE dbo.ewo_sp_WOLookup
    (
    @WODUTFK int=NULL,
    @WOENDDATE datetime = NULL,
    @WOSTATUS char(10) = NULL,
    @DEBUG int=0
    )

    AS

    Declare @intErrorCode int,
    @chvQuery varchar (8000),
    @chvWhere varchar (8000)

    select @intErrorCode = @@ERROR,
    @chvQuery='SET QUOTED_IDENTIFIER OFF select * from WO',
    @chvWhere=''


    -- DUT is specified
    If @intErrorCode = 0 and @WODUTFK is not NULL
    Begin
    set @chvWhere = @chvWhere + 'wo_dutfk = '+@WODUTFK
    End



    IF @intErrorCode=0 and Len(@chvWhere)>0
    Begin
    set @chvQuery = @chvQuery + ' WHERE ' + @chvWhere
    select @intErrorCode=@@ERROR
    END

    IF @DEBUG<>0
    select @chvQuery Query

    IF @intErrorCode=0
    Begin
    exec (@chvQuery)
    select @intErrorCode=@@ERROR
    End

    return @intErrorCode

    GO

    ---

    Any suggestions on how to fix?

    Thanks,

    peter

  2. #2
    Join Date
    Feb 2005
    Posts
    78
    Since the variable @WODUTFK is of type int then when you say

    'wo_dutfk = '+@WODUTFK

    it interpets the + as an addition sign rather than concatenation when you have assigned a non-NULL value to it. You will have to convert @WODUTFK to the correct type before you can use + to concatenate it.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but not permanently. Just in your statement:

    'wo_dutfk = ' + cast(@WODUTFK as varchar(20))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    You sir remain a blind god. Thanks. That did it. BTW - I had tried using cast(), but was trying to send a string and convert it to an int. Thanks for solving - very helpful to me.

  5. #5
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    One other question:

    In the code above - at:

    IF @DEBUG<>0
    select @chvQuery Query


    The output of the @chvQuery in my Query Analyzer Grid is truncated

    Example: In the above code, as I continue specifying fields to pull and expand the where clause - I'm getting:

    SET QUOTED_IDENTIFIER OFF select wo_pk as "WO Number",wo_rempfk as "Requestor",wo_dutfk as "DUT",wo_assignempfk as "Assigned To",wo_labfk as "Lab",wo_compdat as "Desired Complete",wo_status as "Status",wo_subject as "Subject" FROM WO WHERE (wo_rempfk=22 OR

    ie - the system is cutting it off at 'OR'.

    If I use 'SELECT *' instead of the field list above, it properly results in:

    SET QUOTED_IDENTIFIER OFF select * FROM WO WHERE (wo_rempfk=22 OR wo_assignempfk = 88)

    The Query results are fine - it's just the debug display in the grid is truncated. Any way around this? Or should i just debug with 'SELECT *'?

    Thanks,

    Peter

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In Query Analyzer Menu, go to:

    Tools/Options/Results

    ...and increase the Maximum characters per column, which defaults to 256.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Thanks blindgod. Right again. You wanna come to Milwaukee and sit next to me for a week? I'll even spring for the Tequilla.

    Peter

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Tequilla?

    I thought beer was the beverage of choice in Milwaukee.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    We've got plenty of beer too. I find tequilla helps keep me in the near obese category. Beer would push me right into the category.

    Having said that - I'm going out after work for an "after action review" at a tavern down the street. Beer will be the liquid of choice tonight.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pdiotte
    Thanks blindgod. Right again. You wanna come to Milwaukee and sit next to me for a week? I'll even spring for the Tequilla.

    Peter

    Tequila?

    BlindGod?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Pdiotte
    Thanks blindgod. Right again. You wanna come to Milwaukee and sit next to me for a week? I'll even spring for the Tequilla.

    Peter
    Heck, I thought everyone in the Milwaukee area thought that SQL was a mangled form of the Palwaukee transponder code, if they had any clue at all. You mean that there are folks between Brown Deer and the border that think of Structured Query Language when they hear SQL ?!?!

    Just FYI Brett, there's a really GREAT little place just of the Brown Deer Road exit on I-42 that does wonderful Margaritas and does Buffet every Saturday night. You would have been pleased to see the lines outside of Alpine Valley waiting for the Buffet concert last summer, they went literally for miles in both directions!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •