Post reply

Parsing QueryString to SQL Table

  • July 19, 2010 at 1:50 pm

    #223503

    I receive a query string as a nvarchar(max) parameter into a stored procedure. In order to ease my procedure to query against my other tables i want to parse the query string into a table from a string.

    I'm trying to avoid any looping function and if looping function is the only way to do it then please post any techniques to parse the below query string into a temp table.

    Appreciate all your help.

    Thanks.

    userName.name=testuser&

    customer.GenderCode=F&

    customer.GenderCodeDesc=Female&

    customer.Age=25&

    customer.AgeUnit=AgeInYears&

    customer.Preference1=Strawberry&

    customer.Preference2=Blueberry&

    customer.Preference3=Vanilla&

    .....

    .....

    .....

    Expected Results:

    Column1 (Value ) Column2 ( ValueType )

    testuser userName.name

    F customer.GenderCode

    Female customer.GenderCodeDesc

    25 customer.Age

    AgeInYears customer.AgeUnit

    Strawberry customer.Preference1

    Blueberry customer.Preference2

    Vanilla customer.Preference3

  • July 19, 2010 at 2:52 pm

    #1195148

    There was a very big thread about splitting strings last year on this site.

    I'll let you read through that because it covers a lot. Generally, however, the best performance is going to be found via CLR's such as this example.

    Good luck.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 19, 2010 at 2:55 pm

    #1195150

    Thank you...I will dig into this thread right now...

  • July 19, 2010 at 3:23 pm

    #1195158

    Just a heads up on this one to watch out for SQL injection vulnerabilities here. Just parsing a querystring in a stored procedure and using it to dynamically build queries is a bit risky, especially if the calling account has a lot of permissions.

  • July 19, 2010 at 3:33 pm

    #1195163

    You may also find this article by Jeff Moden of great use to you

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 19, 2010 at 3:41 pm

    #1195166

    Nevyn (7/19/2010)

    Just a heads up on this one to watch out for SQL injection vulnerabilities here. Just parsing a querystring in a stored procedure and using it to dynamically build queries is a bit risky, especially if the calling account has a lot of permissions.

    Just use sp_executesql and pass all parameters as part of the @params. Dynamic sql is fine if you're not careless.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 20, 2010 at 5:58 am

    #1195437

    @RJ,

    Are you all set on this?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 20, 2010 at 8:47 am

    #1195579

    Thank you, Ron

    Thank you, Jeff

    Jeff,

    Your article is the exact match for my problem. I was actually thinking about to create a "Static" table which you call it as "Tally" to avoid the lopping actions. I had this thought but didn't had exactly what I'm going to do with the Static tables. But this article gives me lot of ideas & options to nail down all my requirements & saved a lot of my time doing the research.

    These solutions should speed up my transactional procedure to return customer specific data back to users effectively.

    Appreciate all responses.

    Thank You all,

    RJ

  • July 20, 2010 at 4:52 pm

    #1195877

    Thanks for the feedback, RJ. Glad to have been of service.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply