[SQL] Stored Prodedure - INSERT INTO & tabelname is variable

[Equator]

Goed, ik hoop dat ik mijn topictitle duidelijk heb gemaakt.
Server is SQL 2000. Client is Windows 2000.

Ik wil een stored procedure maken, waarin ik een hele simpele INSERT INTO statement laat doen.
(Ik kan dit ook vanuit vbscript, maar dit schijnt veiliger te zijn.)

De stored procedure wil ik niet voor maar 1 tabel gebruiken, maar voor meedere tabellen, die een andere naam hebben.

Ik ben ingelogd met een SQL user die DB_Owner is.

Wanneer ik deze code invoer binnen mijn query analyser:

SQL:

create proc usp_TEST @tblname varchar(64), @cpu0 int, @cpu1 int, @cpu2 int, 
@cpu3 int, @mem dec, @loadof int, @loadss int
as

set nocount on

begin
insert into @tblname (
    colCPU0cntr,
    colCPU1cntr,
    colCPU2cntr,
    colCPU3cntr,
    colMEMcntr,
    colLOADOFcntr,
    colLOADSScntr)
values (
    @cpu0,
    @cpu1,
    @cpu2,
    @cpu3,
    @mem,
    @loadof,
    @loadss)
end

Dan krijg ik de volgende melding:

code:

Server: Msg 137, Level 15, State 2, Procedure usp_TEST, Line 14
Must declare the variable '@tblname'.

Komt in code hierboven overeen met regel 8
En dat terwijl ik de variabele @tblname als parameter al heb gedeclareerd.

Mijn idee was dat hij binnen het statement dus anders om moet gaan met variabelen. (Alhoewel dat voor de rest niet geld)

Wanneer ik bijvoorbeeld de '@' weghaal, dan wordt de stored procedure keurig gemaakt, maar dan probeert hij data weg te schrijven in de tabel "tblname"
En dat is natuurlijk niet de bedoeling.

Kan iemand mij vertellen hoe je de variabele @tblname op moet geven wil het INSERT INTO statement deze als tabelnaam gaan zien.

[P_de_B]

Dit zal alleen werken met dynamische SQL statements die je dan executed.

[Equator]

P_de_B schreef op 24 maart 2004 @ 08:45:
Dit zal alleen werken met dynamische SQL statements die je dan executed.

Leg uit, waarom kan dit niet
Moet ik dan voor elke tabel die ik wil vullen een stored Procedure aanmaken

Das toch eigenlijk onzin.

[Janoz]

Nou, eigenlijk is dat geen onzin. Maar dat jij hetzelfde insert statement kunt gebruiken bij verschillende tabellen doet mij vermoeden dat je een brak datamodel hebt. Het lijkt er namelijk op dat je de naam van de tabel voor meer dingen dan alleen de tabelnaam gebruikt. Ik heb het vermoeden dat de naam die je nu voor tabelnaam wilt gebruiken beter als extra kolom in 1 tabel kunt definieren.

[P_de_B]

Nee dat is geen onzin omdat een 'plan van aanpak' door de query processor wordt gemaakt die specifiek betrekking heeft op de tabel in kwestie. Dit kan dus niet dynamisch.

Wat je zou kunnen doen is iets als het volgende:

code:

declare @sql varchar(1000)
select @sql = 'INSERT INTO ' + @tablename + ' values etc. etc.'
EXEC (@sql)

Dit is echter niet een hele goede manier, je zou dan gewoon beter in je programma het statement op kunnen bouwen. Als je wel met sp's wilt werken moet je voor elke tabel een sp maken. Tegenwoordig zijn de snelheidsvoordelen van een sp niet echt meer aanwezig, dynamische sql is wellicht een betere keuze.

[whoami]

Werkt dat met EXEC ?
Ik denk dat je sp_executesql moet gebruiken.

Dit is echter niet een hele goede manier, je zou dan gewoon beter in je programma het statement op kunnen bouwen. Als je wel met sp's wilt werken moet je voor elke tabel een sp maken. Tegenwoordig zijn de snelheidsvoordelen van een sp niet echt meer aanwezig, dynamische sql is wellicht een betere keuze.

Helemaal mee eens is.

[P_de_B]

Dat werkt gewoon met EXEC hoor, sp_executesql is in gevallen waar je een parameter gebruikt (en verder gelijke queries) beter omdat het opgeslagen queryplan hergebruikt kan worden.


sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.

[ Voor 76% gewijzigd door P_de_B op 24-03-2004 09:07 ]

[EfBe]

CyberJ schreef op 24 maart 2004 @ 08:44:
Ik wil een stored procedure maken, waarin ik een hele simpele INSERT INTO statement laat doen.
(Ik kan dit ook vanuit vbscript, maar dit schijnt veiliger te zijn.)

De stored procedure wil ik niet voor maar 1 tabel gebruiken, maar voor meedere tabellen, die een andere naam hebben.

Kan niet. Gebruik een generator voor de procs voor de tables. Je kunt die insert procs ook middels enterprise manager maken btw.

[jvdmeer]

Heb je wel eens van normaliseren gehoord? Zet de gegevens die dezelfde vorm bevatten in 1 tabel:
Dus de volgende velden:

code:

colSrttabel
colCPU0cntr
colCPU1cntr
colCPU2cntr
colCPU3cntr
colMEMcntr
colLOADOFcntr
colLOADSScntr

en dan kan je de stored procedure gewoon gebruiken:

SQL:

create proc usp_TEST @tblname varchar(64), @cpu0 int, @cpu1 int, @cpu2 int, 
@cpu3 int, @mem dec, @loadof int, @loadss int
as

set nocount on

begin
insert into Tabel(colSrtTabel,
    colCPU0cntr,
    colCPU1cntr,
    colCPU2cntr,
    colCPU3cntr,
    colMEMcntr,
    colLOADOFcntr,
    colLOADSScntr)
values (   @tblname,
    @cpu0,
    @cpu1,
    @cpu2,
    @cpu3,
    @mem,
    @loadof,
    @loadss)
end

[Equator]

OK, Ik snap enigzins wat er bedoeld wordt.
Mijn datamodel brak.. Ik geloof je direct. Ik ben (nog) niet zo into databases.
Ik haal via vbscript & WMI performance gegevens op en stop deze in een SQL database, om er later weer rapporten uit te halen.

IK heb voor elke SERVER die ik monitor een tabel aangemaakt.
tbl_PERFCNTRS_<SERVERNAME>
In elk van die tabellen staan de kolommen:
colCPU0cntr -> int
colCPU1cntr -> int
colCPU2cntr -> int
colCPU3cntr -> int
colMEMcntr -> decimal(9)
colLOFcntr -> int
colLSScntr -> int
TimeDate -> datetime: Default value getdate()

In mijn VBscript doe ik momenteel de waardes via:
conn.execute(INSERT INTO tbl_PERFCNTRS_" & strServername & " (col...etc..)

Maar een vriend van mij (Hij is MS SQL DBA) meldde dat je dat beter via een stored procedure kan doen, omdat het anders ook mogelijk is dat je een XP_CommandPrompt oid commando mee kan geven.

Maar ik dacht dat ik deze stored proc ook wel dusdanig flexibel kon maken zodat ik niet voor elke tabel een aparte sp moest maken..

[whoami]

Embedded parametrized queries zijn even veilig als stored procedures.

[P_de_B]

Dit is inderdaad niet goed genormaliseerd. Je kunt beter

ServerName, CounterA, CounterB, CounterC etc doen

Waar je vriend voor waarschuwde is SQL Injection, dit is iets waar je wel rekening mee moet houden. Echter, SP's zijn niet de enige oplossing daarvoor, geparameteriseerde queries werken ook heel goed. Hierover is in de search ook veel te vinden.

[Equator]

OK, ik heb dat over die parametrized queries bekeken, en tig topics doorlopen, en ik kwam binnen vbscript tot de volgende code:

code:

Set wshShell = CreateObject("Wscript.Shell")

set conn = CreateObject("ADODB.Connection")
conn.open("PROVIDER=SQLOLEDB;DATA SOURCE=SERVER;UID=priv;PWD=private;DATABASE=Performance-Counters")

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection=conn
cmd.CreateParameter "valCPU0", 3
cmd.CreateParameter "valCPU1", 3
cmd.CreateParameter "valCPU2", 3
cmd.CreateParameter "valCPU3", 3
cmd.CreateParameter "valMU", 14
cmd.CreateParameter "valOF", 3
cmd.CreateParameter "valSS", 3

valcpu0=InputBox("valcpu0")
valcpu1=InputBox("valcpu1")
valcpu2=InputBox("valcpu2")
valcpu3=InputBox("valcpu3")
valmu=InputBox("valmu")
valof=InputBox("valof")
valss=InputBox("valss")
cmd.CommandText = "INSERT INTO tbl_TEST (colCPU0cntr, colCPU1cntr, 
colCPU2cntr, colCPU3cntr, colMEMcntr, colLOFcntr, colLSScntr) VALUES (" & "'" & 
valCPU0 & "'" & "," & "'" & valCPU1& "'" & "," & "'" & valCPU2 & "'" & "," & "'" & 
valCPU3 & "'" & "," & "'" & valMU & "'" & "," & "'" & valOF & "'" & "," & "'" & 
valSS& "'" & ")"

WScript.Echo cmd.CommandText
cmd.Execute

De code werkt, en de waardes worden ingevoerd.

Maar vorkom ik hiermee nu de mogelijkheid tot SQL Injection

[ Voor 52% gewijzigd door Equator op 24-03-2004 11:47 ]

[P_de_B]

Je moet geen string concatenation gebruiken, maar parameters. www.4guysfromrolla.com heeft een aantal voorbeelden.