Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: sp_executesql for dummies

    I have a stored procedure using dynamic SQL and I've been told I can no longer use 'exec (@strsql)' and have to use sp_executesql instead.

    Fiiiiiiine...except I don't know how to use it and make it work with the parameter I'm passing in.

    The original sproc that I have to change reads as:


    CREATE PROCEDURE dbo.sp_LetsGetSomeData(@Filter nvarchar(200) = NULL)
    AS

    DECLARE @strSQL nvarchar(200)

    IF @Filter IS NOT NULL
    BEGIN
    SET @strSQL = N'SELECT * FROM vwRandomViewName ' + @Filter
    END

    IF @Filter IS NULL
    BEGIN
    SET @strSQL = N'SELECT * FROM vwRandomViewName'
    END

    EXEC(@strSQL)


    GO


    And is called thusly:

    EXEC sp_LetsGetSomeData @Filter = ' WHERE Team = ''RandomTeam'''

    When I do this it never adds the filter. I tried to write a sproc like this:

    CREATE PROC dbo.LetsGetSomeData2(@Filter nvarchar(200) = NULL)
    AS

    DECLARE @strSQL nvarchar(200)

    SET @strSQL = N'SELECT * FROM vwRandomView'

    EXECUTE sp_executesql @strSQL, @Filter
    GO


    and call it the same way as the original flavor sproc, it's as if I'm asking it to just select all from vwRandomView.

    I know I'm doing something massively wrong but I'm just having a brain-dead day and can't make sense of the books online.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    please see the sp_executesql documentation in Books Online. You need to add your filter to the string concatenation for your sql string. However column names need to be added as paramters to the call to sp_executesql. Again see the documentation.
    “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.

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Thrasymachus
    please see the sp_executesql documentation in Books Online. You need to add your filter to the string concatenation for your sql string. However column names need to be added as paramters to the call to sp_executesql. Again see the documentation.

    Thrasymachus, I said that I already read the documentation but I still need help.

    So...um...thanks.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    So try this:

    Code:
    CREATE PROC dbo.LetsGetSomeData2(@Filter nvarchar(200) = NULL)
    AS
     
    DECLARE @strSQL nvarchar(200)
     
    SET @strSQL = N'SELECT * FROM vwRandomView ' + ISNULL (@Filter,'')
     
    EXECUTE sp_executesql @strSQL
    GO

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Brett Kaiser
    Nahhhh ... that's worth at least $1.02

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    It's OK, I already figured it out on my own. Thanks.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    did you read my link?

    Sounds like a 'tude
    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.

  9. #9
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Brett Kaiser
    did you read my link?

    Sounds like a 'tude

    I didn't read it before I figured it out myself, no; but I did read it. Sorry if it seems as if I have a "tude" but in my original post I did say that I found the books online explanation less than helpful and the first response to my post referred me to the books online...so I was slightly annoyed by that.

Posting Permissions

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