SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored proc with variables


Stored proc with variables

Author
Message
Benny Buteneers
Benny Buteneers
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 49
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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109995 Visits: 18620
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 Buteneers
Benny Buteneers
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 49
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
John Mitchell-245523
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109995 Visits: 18620
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 Buteneers
Benny Buteneers
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 49
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
Sean Pearce
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12305 Visits: 3512
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

@SeanPearceSQL

About Me
GilaMonster
GilaMonster
SSC Guru
SSC Guru (756K reputation)SSC Guru (756K reputation)SSC Guru (756K reputation)SSC Guru (756K reputation)SSC Guru (756K reputation)SSC Guru (756K reputation)SSC Guru (756K reputation)SSC Guru (756K reputation)

Group: General Forum Members
Points: 756489 Visits: 48477
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 Buteneers
Benny Buteneers
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 49
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search