Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Dynamic SQL Help

    I started this thread else where but I dont quite understand what it all saying, dont know if its how I have put it accross or not but cant seem to go any further on it.

    All I'm looking to do is create a dynamic SQL so that the StaffID can be changed through the EXEC Statement

    SELECT ContactServiceID, StaffID
    FROM PARAMED_TEMP
    WHERE (StaffID Like 'P%')
    AND ContactServiceID IS Null

    The ContactServiceID will always be NULL, the only thing to change would be the StaffID

    How can I do this?

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112685

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

    Thanks for pointing out the cross post

    Quote Originally Posted by JezLisle
    The ContactServiceID will always be NULL, the only thing to change would be the StaffID
    This last bit is key and has been throwing those guys. To start - they are spot on - you don't need (nor should you ever want) to use dynamic SQL for this. Several reasons - plan caching, debugging, SQL Injection.

    I'm stealing visakh's code here:
    Code:
    ALTER PROCEDURE [jez].[sp_ZZ]
    
    @StaffID VARCHAR(10)
    
    AS
    
    SELECT ContactServiceID, StaffID FROM PARAMED_TEMP 
    WHERE StaffID Like @StaffID
    AND ContactServiceID IS NULL
    GO
    
    EXEC  Null,'P%'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots what kind of name is sp_ZZ!

    How rude.
    not to mention poor naming convention... sp prefix indeed!
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I see from the link that it was not you that came up with the name, apologies where apologies due!
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like the way viskah has set up a schema called Jez: just how many questions have you asked over there fella?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I see from the link that it was not you that came up with the name, apologies where apologies due!
    Bookmarked for future out-of-context quoting.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Quote Originally Posted by pootle flump
    Hi Jez

    Thanks for pointing out the cross post

    This last bit is key and has been throwing those guys. To start - they are spot on - you don't need (nor should you ever want) to use dynamic SQL for this. Several reasons - plan caching, debugging, SQL Injection.

    I'm stealing visakh's code here:
    Code:
    ALTER PROCEDURE [jez].[sp_ZZ]
    
    @StaffID VARCHAR(10)
    
    AS
    
    SELECT ContactServiceID, StaffID FROM PARAMED_TEMP 
    WHERE StaffID Like @StaffID
    AND ContactServiceID IS NULL
    GO
    
    EXEC  Null,'P%'
    Excellent Thanks that now makes sense and has worked, well when I got rid of the Null in the EXEC Statement. Now if I was to have more than one Staff ID I wanted to search on, how would I have that in the Statement,

    eg Like 'CH[0-9]%' And <>'ch42' Or Like 'FC%'

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think it's worse than that fella. Look at the operators in his example! In fact Jez - your example would make zero sense without some brackets.

    Jez - you are defo into dynamic SQL here. I guess playing around with the link above you could build up code that does not directly execute user input but you will have fun biulding it. I think you are down to concatenating your entire where clause into the statement and executing that.

    There be dragons however: you'll want to google SQL Injection for starters.
    Sticking with dportas' lead you'll want to read this:
    http://www.sommarskog.se/dynamic_sql.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I've had a read through the link
    http://www.sommarskog.se/arrays-in-sql-2005.html

    This is what I've come up with, the SP seems to do what I want but on the EXEC statement I get an error message, which I dont understand as its looking for me to declare a variable which I have already declared...

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    ALTER Procedure [jez].[Test]
    	@StaffID VARCHAR(10),
    	@ContServID VARCHAR(50)
    AS
    Declare @SQL VarChar(1000)
    
    Select @SQL = 'UPDATE jez.PARAMED_TEMP SET jez.PARAMED_TEMP.ContactServiceID = @ContServID '
    Select @SQL = @SQL + 'WHERE StaffID LIKE (' + @staffID +') AND (ContactServiceID IS NULL) '
    
    Exec (@SQL)
    Code:
    EXEC jez.Test 'L36%', 'LEARN'
    Error Message

    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@ContServID".

    what do I need do to get around this?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - a few things:
    There's no need for dynamic SQL for that statement. The reason I said use dynamic SQL was because of this:
    Quote Originally Posted by JezLisle
    Now if I was to have more than one Staff ID I wanted to search on, how would I have that in the Statement,

    eg Like 'CH[0-9]%' And <>'ch42' Or Like 'FC%'
    Your new proc could not actually handle this. So - do you still need to account for the above?

    In short re the declaration: when you run dynamic SQL using EXEC() you execute it within its own scope. This means that since you concatenated the name of @ContServID and not the value you get your error.

    Whatever you do - PRINT out the value of @SQL when coding - don't EXEC it. Print it out and check it looks ok - correct if required. There are actually more errors in your code but we need to sort out other stuff first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    In this line 'WHERE StaffID LIKE (' + @staffID +') I have done this because, the end user may have one or many @StaffID to enter...

    If I have more than one @staffID is what I've done not the correct way?

    I'm not sure why its concatenated the @ContServID as this needs to be either NULL or actually a ContactServiceID

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    How are you intending to pass more than one value into your proc?

    The reason your example failed was that you haven't declared the variables inside the dynamic EXEC string. If you use sp_executesql instead of just EXEC then the variables will be declared for you. But as already pointed out, you don't even need to use dynamic SQL in the example you have given.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - with the example you gave you did not simply provide a list of staff ids - you included operators too (<>, LIKE). Concatenate that example into your code and you get:
    Code:
    ....
    WHERE StaffID LIKE ('CH[0-9]%' And <>'ch42' Or Like 'FC%') AND (ContactServiceID IS NULL)
    That will not work.

    You need something like:
    Code:
    Select @SQL = @SQL + 'WHERE ' + @staffID + ' AND ContactServiceID IS NULL'
    and now @staffID has to be more like a portion of a SQL statement rather than some values.
    Code:
    SET @staffID = ' (staffID Like ''CH[0-9]%'' Or staffID Like ''FC%'') AND staffID <> ''ch42'' '
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    I'm not sure why its concatenated the @ContServID as this needs to be either NULL or actually a ContactServiceID
    Probably because that's how you coded it
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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