Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Dynamic SQL Help needed.

    I have the following stored procedure :
    Code:
    CREATE PROCEDURE usp_CraneRental_UpdateRate
    @iEntryID int,
    @cField sysname,
    @cValue varchar(100),
    @dtModified datetime OUTPUT
    
    AS
    
    SELECT @dtModified = GetDate()
    
    DECLARE @cSql VARCHAR(500)
    
    SELECT @cSql = 'UPDATE tbCraneRentalRates SET ' + @cField + ' = ' + RTRIM(@cValue) +
    		   ', Modified = ' + '"' + CAST(@dtModified AS VARCHAR) + '"' +  
    	 	   ' WHERE EntryID = ' + CAST(@iEntryID AS VARCHAR)
    
    EXEC(@cSql)
    IF(@@ERROR <> 0 OR @@ROWCOUNT <= 0)
    	RAISERROR('Failed to update transportation rate!',16,1)
    GO
    that generates the following SQL String :
    Code:
    UPDATE tbCraneRentalRates SET MoveIn = 0, Modified = "May 20 2004 9:59 AM" WHERE EntryID = 1
    The error I am getting is:
    Code:
    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'May 20 2004  9:59AM'.
    ?????
    This doesn't make sence, the SQL statement looks perfectly fine ?

    Any help?
    Mike B

  2. #2
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    try using 2 ' quotes to make it create ', youre giving it the text in speach " " marks which sql doesn't like.

    try '' should make it display '

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    put this before the create statement in qa:

    set quoted_identifiers off

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by sgmuse
    try using 2 ' quotes to make it create ', youre giving it the text in speach " " marks which sql doesn't like.

    try '' should make it display '
    That works, thanks.

    Mike B

Posting Permissions

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