Stored proc with variables

Question

Stored proc with variables

benny 52509

SSC Enthusiast

Points: 141
More actions
February 5, 2018 at 7:49 am

#343481

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

benny 52509 SSC Enthusiast Points: 141 More actions · Answer 2

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

benny 52509 SSC Enthusiast Points: 141 More actions · Answer 4

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

benny 52509 SSC Enthusiast Points: 141 More actions · Answer 7

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