Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: can you pass field names in stored procs?

    Code:
    CREATE PROCEDURE [dbo].[removeContact]
    
    @RemoveType VARCHAR (50),
    @ListID INT,
    @TargetField VARCHAR (50),
    @TargetValue VARCHAR (150),
    @RowsAffected INT OUTPUT
    
    AS
    
    UPDATE [tblContacts]
    SET @RemoveType = 'yes'
    WHERE [list_id]=@ListID
    AND @TargetField = @TargetValue
    
    SELECT @RowsAffected = @@ROWCOUNT
    GO
    Code:
    declare @P1 int
    set @P1=0
    exec removeContact '[unsubscribe]', 6, '[email]', 'email@domain.com', @P1 output
    select @P1
    I've tried this with and without the square brackets. Either way, nothing gets updated. Am I barking up the wrong tree?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Spudhead

    You will need to use dynamic SQL for this. Check out dynamic SQL in BoL.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Ok, cheers. I found something about concatenating SQL strings together, but I seem to have tripped over a syntax error:

    Code:
    DECLARE @SQLString NVARCHAR(500)
    
    SET @SQLString = 'UPDATE [tblContacts] SET ' + @RemoveType + '=''yes'' WHERE [list_id]=' + CAST(@ListID AS nvarchar) + ' AND ' + @TargetField + ' = ' + @TargetValue
    
    EXEC sp_executesql @SQLString
    Code:
    exec removeContact '[unsubscribe]', 6, '[email]', 'email@domain.com'

    I can't seem to get round escaping the quotes for @TargetValue - as it is above, I get an error: "The column prefix 'email@domain' does not match with a table name or alias name used in the query." I've tried pretty well every other combination of quotes I can think of, with no luck.

    What simple solution am I missing?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you need to go to BOL and look at the documentation for sp_executesql a little closer. you are going to have to pass the fieldname to the procedure as a parameter.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Always worth printing the string too before you execute it. You can then run in QA. It might be a copy and paste error but there is no space after WHERE too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    Right... I'm getting in well over my head here

    This is what I've got:

    Code:
    DECLARE @RemoveType VARCHAR (50)
    DECLARE @ListID INT
    DECLARE @TargetField VARCHAR (50)
    DECLARE @TargetValue VARCHAR (150)
    SET @RemoveType = '[unsubscribe]'
    SET @ListID = 6
    SET @TargetField = '[email]'
    SET @TargetValue = 'email@domain.com'
    
    EXEC sp_executesql N'UPDATE [tblContacts] SET @P_RemoveType=''yes'' WHERE [list_id] = @P_ListID AND @P_TargetField = @P_TargetValue',  N'@P_RemoveType VARCHAR (50), @P_ListID INT, @P_TargetField VARCHAR (50), @P_TargetValue VARCHAR (50)', @RemoveType, @ListID, @TargetField, @TargetValue
    To be honest, I've only pasted that version up here because it's the first one that's just given me a "0 rows affected" message rather than an error.

    Am I even CLOSE? I've read through the documentation for the past hour and ... well, it makes no sense to me I think I've figured out about the unicode strings, and the parameter substitution, but... well, it still isn't doing what I want it to.

    I'm as much an advocate of learning by your mistakes as the next bloke, but I'm reaching the point where I'm just pleading with someone to show me. I'm just stabbing blindly in the dark here.

  7. #7
    Join Date
    Jan 2002
    Posts
    189
    Quote Originally Posted by pootle flump
    Always worth printing the string too before you execute it. You can then run in QA.
    Yeah... if only I knew how...

    Quote Originally Posted by pootle flump
    It might be a copy and paste error but there is no space after WHERE too.
    c&p error. It wouldn't let me delete it

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    DECLARE @RemoveType VARCHAR (50)
    DECLARE @ListID INT
    DECLARE @TargetField VARCHAR (50)
    DECLARE @TargetValue VARCHAR (150)
    SET @RemoveType = '[unsubscribe]'
    SET @ListID = 6
    SET @TargetField = '[email]'
    SET @TargetValue = 'email@domain.com'
    
    declare	@SQLString varchar(8000)
    
    set	@SQLString = 
    N'UPDATE [tblContacts]
    SET ' + @RemoveType + '=''yes''
    WHERE[list_id] = ' + convert(varchar(50), @ListID) + '
    	AND ' + @TargetField + ' = ' + @TargetValue + ''
    
    select	@SQLString --Just for testing, to see the statement that will be executed.
    
    --EXEC (@SQLSTRING)
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oops. You need to handle the quotes differently:
    Code:
    DECLARE @RemoveType VARCHAR (50)
    DECLARE @ListID INT
    DECLARE @TargetField VARCHAR (50)
    DECLARE @TargetValue VARCHAR (150)
    SET @RemoveType = '[unsubscribe]'
    SET @ListID = 6
    SET @TargetField = '[email]'
    SET @TargetValue = 'email@domain.com'
    
    declare	@SQLString varchar(8000)
    
    set	@SQLString = 
    N'UPDATE [tblContacts]
    SET ' + @RemoveType + '=''yes''
    WHERE [list_id] = ' + convert(varchar(50), @ListID) + '
    	AND ' + @TargetField + ' = ''' + @TargetValue + ''''
    
    select	@SQLString --Just for testing, to see the statement that will be executed.
    
    --EXEC (@SQLSTRING)
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Jan 2002
    Posts
    189
    Yeah, I found that - I just kept adding quotes onto the end until it was happy

    Many thanks, though, to all for your help. For the benefit of anyone who's still interested, this is my working* version:

    Code:
    CREATE PROCEDURE [dbo].[removeContact]
    
    @RemoveType VARCHAR (50),
    @ListID INT,
    @TargetField VARCHAR (50),
    @TargetValue VARCHAR (150),
    @RowsAffected INT OUTPUT
    
    AS
    
    declare	@SQLString varchar(8000)
    
    set	@SQLString = N'UPDATE [tblContacts] SET ' + @RemoveType + '=''yes'' WHERE [list_id] = ' + convert(varchar(50), @ListID) + ' AND ' + @TargetField + ' = ''' + @TargetValue + ''''
    
    EXEC (@SQLSTRING)
    
    SELECT @RowsAffected = @@ROWCOUNT
    GO
    * "working", as in "it's close enough at 5pm on a Friday when the footy is on"


Posting Permissions

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