Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    7

    Unanswered: Passing SQL qurey as parameters

    I am want to pass SQL query as arguments to DB and then execute them from SQL server, also, use the same principle to modify the query according to some conditions. The following is the code I tried, which dosen't show any errors while parsing, the stored procedure is created without any errors, but while trying to execute the stored procedure..... all hell breaks loose
    Code:
    PROCEDURE [dbo].[proc_ser1_createdby]
    (
     @var_createdby varchar(50) 
    ,@var_cartoon varchar(50)
    ,@var_married varchar(50) 
    ,@var_sql varchar(50) OUTPUT)
    AS 
     	BEGIN     
    		SELECT @var_sql=' '
    select @var_sql = 'SELECT var_userid,var_stream tab_userdetails where  var_cartoon = '+@var_cartoon+' AND var_createdby = ' + @var_createdby + '
    		var_married= '+@var_married 
    		
     	exec (@var_sql) 
    
    	END



    Have actually tried many versions of the same, but unable to solve it On execution, I get the following message:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'where'.

    (1 row(s) affected)

    (1 row(s) affected)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ALTER PROCEDURE dbo.proc_ser1_createdby (
       @var_createdby varchar(50) 
     , @var_cartoon   varchar(50)
     , @var_married   varchar(50) 
     , @var_sql       varchar(50) OUTPUT
    )
    AS 
     	BEGIN
    
        SELECT var_userid
             , var_stream
             , tab_userdetails
        --you missed the FROM clause!
        FROM   <your_table>
        WHERE  var_cartoon = @var_cartoon
        AND    var_createdby = @var_createdby
        --You also missed the second AND!
        AND    var_marrid = @var_married
    
    	END
    GO
    You do not use parameters in the way you have attempted - that proc is still subject to sql-injection and as such is a security threat.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2009
    Posts
    7

    Smile Thanks, but still unable to solve the issue!!

    Thank you George , I din't had a clue about the SQL injection attack. I am really grateful for your input But then if not for the above mentioned code how can I determine and search for conditions when I am not able to predict the number of parameters that would be passed, it may vary from 0 to n number of variables. What to do in such a scenario??

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2009
    Posts
    7

    version of sql server

    I am working on SQL Server 2005, sorry for the delay, but I would really like to know how to solve the issue of search when the parameter may contain many values and they are unknown. I would like to do it without having to write the"if" conditions for each possible parameter, for checking if it is null or not. My other concern is how to avoid the SQL Injection attack if I am passing it from web form as bits and piece of the sql query?

  6. #6
    Join Date
    Feb 2009
    Posts
    51

    Cheking for null

    I think best way to is check all the parameters for seperately for nulls
    individually using If Stmt.

    I have done checking like that for nearly 30 - 40 parameters
    and nearly couple hundred procedures.
    Only Thing is getting standard and one common statement line.
    Then it is just replacing the @variable names
    Good thing is all the procedure codes will be consistant.

    Good fun though
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  7. #7
    Join Date
    Jun 2009
    Posts
    7

    Checking for all the parameters for seperately.

    Quote Originally Posted by dbfHelp
    I think best way to is check all the parameters for seperately for nulls
    individually using If Stmt.

    I have done checking like that for nearly 30 - 40 parameters
    and nearly couple hundred procedures.
    Only Thing is getting standard and one common statement line.
    Then it is just replacing the @variable names
    Good thing is all the procedure codes will be consistant.

    Good fun though
    Would you please show an example code, for checking the parameters separately. If it can be done and the result generated in a single SqlDataReader then all my problems are solved.

  8. #8
    Join Date
    Jul 2009
    Posts
    1
    where is the from clause

Posting Permissions

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