Post reply

Urgent:How to pass a column name as a parameter to stored proc

  • August 30, 2006 at 10:01 am

    #173924

    Hi,

          Here is the senario.

          I have 2 tables with same stucture(with same no. of columns, same column names and the same no. of records)

    so i would like to pass this column as a variable name to a stored proc

    and want to do something like this

    CREATE PROC SP_InsUpdateExcp

     @field VARCHAR(3)

      AS

      SELECT A.ID_TOWN, A.@field, B.@field, GETDATE()  FROM SampleTable1 A

      INNER JOIN SampleTable2 B

      ON A.ID_TOWN = B.ID_TOWN

      WHERE A.@field != A.@field

    GO

    But this is not gonna work ... can anyone sujjest me a solution for the above problem

  • August 30, 2006 at 10:44 am

    #657630

    Why can't you just use a regular statement and create one Sp per statement?

  • August 30, 2006 at 10:54 am

    #657635

    Use dynamic sql in procedure

  • August 30, 2006 at 11:00 am

    #657639

    I would really use that as a LAST RESORT.  In 2000+ sps created I only had to use dynamic sql once, and it was for admin tasks.

  • August 30, 2006 at 11:09 am

    #657642

    I really like to know the approaches..

    which can be used in this situation .

    I understand the situation as  Creating the resultset based on user requested columns .

  • August 30, 2006 at 11:15 am

    #657646

    In such a case.  You use views to block access to base tables and grant access on them.

     

    Then you code a query builder and secure the heck out of it (Forbid words like ";", go, delete, insert, update, alter, create, etc).  Then you let the application generate the sql statement.  But this is really something I rarely let the users do... you need a query, come see me and I'll build it for you (so they don't have to waste time learning the tables system).

  • August 30, 2006 at 11:21 am

    #657648

    Thanks for your quick response..

    what are the disadvantages of using dynamic sql in stored procedure over your specified approach ( I really didn't get it properly)

     

  • August 30, 2006 at 11:29 am

    #657650

    Basically if you generate the statement like this :

    Select * from dbo.Users where username = '" & txtUsers & "' and pass = " & txtPass & "'"

    All the user as to do to access you site is this :

    txtUser = "admin"

    txtPass = " ' or '1' <> ' "

     

    So your statement has been altered to become this :

    Select * from dbo.Users where username = 'admin' and pass = '' or '1' <> ''

     

    Boom access granted.  Both the user could do much more than that if no security is in place... create tables, drop tables.  alter the price of products.

  • August 30, 2006 at 11:39 am

    #657653

    I appreciate the explanation..

    I guess you are talking about cons of dynamic sql.. Any way our situation is not this.. we are selecting columns dynamically from a table

    I agree that dynamic sql is a double edged sword, it can be used rather carefully

    Thanks for nice explanation

     

  • August 30, 2006 at 11:50 am

    #657656

    Same difference.  Nothing stops the user from doing anything he wants.

     

    the statement can be split like...

     

    Select * from dbo.SysObjects ; drop table products ; select...

     

    Nothing forbids him of doing that either... It's only limited by his imagination and your security measures.

  • August 30, 2006 at 12:03 pm

    #657658

    I am totally agree with you..

    But with careful validation of User Input at Front End Side and in Stored Procedure we can eliminate this type of vulnerabilities

    Basically I like dynamic sql, because it adds new dimension to the sql

     

  • August 30, 2006 at 12:07 pm

    #657659

    I've never seen a way to have total security with dynamic sql.. that's why i don't use it among other things.

  • August 30, 2006 at 8:43 pm

    #657718

    SELECT A.ID_TOWN,

    CASE @field WHEN 'Col01' then A.Col01 WHEN 'Col02' then A.Col02 WHEN ... END,

    CASE @field WHEN 'Col01' then B.Col01 WHEN 'Col02' then B.Col02 WHEN ... END, 

    GETDATE()  FROM SampleTable1 A

      INNER JOIN SampleTable2 B

      ON A.ID_TOWN = B.ID_TOWN

    _____________
    Code for TallyGenerator

  • August 31, 2006 at 6:44 am

    #657775

    Seems like a nice comprosime... but I hope you don't have 120 columns on that table because that'd be a b**ch to code .

  • August 31, 2006 at 6:52 am

    #657783

    "what are the disadvantages of using dynamic sql in stored procedure over your specified approach ( I really didn't get it properly) "

    You are sacrificing the significant performance advantages of a stored proc, such as precompiled query execution plan.

    Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

Viewing 15 posts - 1 through 14 (of 14 total)

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