Stored proc with variables

  • Heloo, i have a problem with the follow storedproc
    in my VB program i have a lot of tables with SQL Server 2014
    ith this storedproc i try to consider of a name really exist in one table.
    USE [Eurosort]
    GO
    /****** Object:  StoredProcedure [dbo].[S_Getsearchname]]    Script Date: 5/02/2018 12:56:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:  <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER   PROCEDURE [dbo].[S_Getsearchname]
     @dbname  as nvarchar(50) ,
     @Searchname as nvarchar(30) AS
    BEGIN
    DECLARE @sqlCommand varchar(max)
    SET @sqlCommand = 'SELECT * from '+ @dbname  +' Where naam = ' + RTRIM(@Searchname)
    EXEC (@sqlCommand)  
    END
    --------------------------------------------------------------------------------------------------------
    when i executed the storedproc
    USE [Eurosort]
    GO
    DECLARE @return_value int
    EXEC @return_value = [dbo].[S_Getsearchname]
      @dbname = N'Leeggoed',
      @Searchname = N'PAK'
    SELECT 'Return Value' = @return_value
    GO

     i got Always the msg
    Msg 207, Level 16, State 1, Line 3
    Invalid column name 'PAK'.
    ven wich table i use 
    the proc accept not the var @searchname

    Ken er nog niet veel van, maar leer het wel
    Do not know much about it yet, but learn

  • So you want to see whether the Searchname appears anywhere in the database?  To do that you'd need to loop through every character column in every table.  If you have a large database, that's going to be very slow.  You might be better off using full text indexing.

    But before you go ahead with anything, make sure you read about and understand SQL injection.  The way you're trying to do this is, as it stands, a massive security risk.

    John

  • Dear John 
    i  try only to look in the table @dbname
    without the variable @Searchname its working perfect
    i got  all the data from this table

    DECLARE @sqlCommand varchar(max)
    SET @sqlCommand = 'SELECT * from '+ @dbname 
    EXEC (@sqlCommand)

    Ken er nog niet veel van, maar leer het wel
    Do not know much about it yet, but learn

  • OK, I see.  You should change that parameter name to @TableName, so it's clear what it is.  And are you looking for the Searchname only in the column naam?

    If so, this should work:SET @sqlCommand = 'SELECT * from '+ @dbname +' Where naam = ''' + RTRIM(@Searchname) + ''''

    BUT you are still vulnerable to SQL injection.  If you're the kind of person who doesn't like reading his own name in the news, please address this before you do anything else.

    John

  • Thx John
    you helped me very fast,
    and i go try to do something on this injection,
    but the reason that i will use a stordeproc like this:
    i use  a lot of comboboxes in my program
    on this way the user can type a new name or change a name  in the box and when
    this name not exist de 2é part of the storedproc get add this name in the table
    and  nobody see the result of the first part of my  storedproc

    Thx for you help

    Ken er nog niet veel van, maar leer het wel
    Do not know much about it yet, but learn

  • DECLARE
        @dbname SYSNAME = 'master.sys.databases',
        @Searchname SYSNAME = 'model';

    DECLARE @sqlCommand NVARCHAR(MAX);
    SET @sqlCommand = 'SELECT * FROM ' + @dbname + ' WHERE name = @Searchname';

    EXEC sp_executesql @sqlCommand, N'@Searchname SYSNAME', @Searchname = @Searchname;

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • I strongly recommend that you use one procedure for one thing. No dynamic code. Or use an ORM for those drop downs
    Yes, it's some up-front work, but it makes it easier later when you need to do something special in one case. And it's not vulnerable to having your entire database stolen or deleted (or both)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thx Gail,
    you are the second, who advice me for not use dynamic code
    i'am a beginner and every advice is very welcome,
    So i go try one other solution for my drop down boxes

    Ken er nog niet veel van, maar leer het wel
    Do not know much about it yet, but learn

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

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