Stored Proc Newbie
-
I'm trying to create a stored procedure which can insert data into a number of different tables. But the SQL parser always gives
error 137 must declare the variable '@whichtable'What am I doing wrong? Help please!:confused:CREATE PROCEDURE dbo.new_zone @whichtable varchar(20), @num int, @name varchar(20) AS INSERT @whichtable (n,nme) VALUES (@num,@name) GO -
I'm trying to create a stored procedure which can insert data into a number of different tables. But the SQL parser always gives
error 137 must declare the variable '@whichtable'What am I doing wrong? Help please!:confused:CREATE PROCEDURE dbo.new_zone @whichtable varchar(20), @num int, @name varchar(20) AS INSERT @whichtable (n,nme) VALUES (@num,@name) GOchris fearnley wrote: INSERT @whichtable (n,nme) VALUES (@num,@name) I don't think you can substiture a variable for a table name - it messes with SQL Server's ability to pre-compile stored procedures then use the compiled version. What you need to do is create something like:
IF @whichtable = 'TableA'
INSERT TableA (n,nme) VALUES (@num,@name)
ELSE IF @whichtable = 'TableB'
INSERT TableB (n,nme) VALUES (@num,@name)
ELSE IF @whichtable = 'TableC'
INSERT TableC (n,nme) VALUES (@num,@name)There is another method whereby you can create a string containing the relevant SQL and get it to parse and execute the string, but I don't recall off the top of my head how to do that. Also, if performance is important you don't want stored procedures parsing SQL midflight. Does this help?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar "On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871)
-
chris fearnley wrote: INSERT @whichtable (n,nme) VALUES (@num,@name) I don't think you can substiture a variable for a table name - it messes with SQL Server's ability to pre-compile stored procedures then use the compiled version. What you need to do is create something like:
IF @whichtable = 'TableA'
INSERT TableA (n,nme) VALUES (@num,@name)
ELSE IF @whichtable = 'TableB'
INSERT TableB (n,nme) VALUES (@num,@name)
ELSE IF @whichtable = 'TableC'
INSERT TableC (n,nme) VALUES (@num,@name)There is another method whereby you can create a string containing the relevant SQL and get it to parse and execute the string, but I don't recall off the top of my head how to do that. Also, if performance is important you don't want stored procedures parsing SQL midflight. Does this help?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar "On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871)
Thanks Colin, What you say makes complete sense - but that gives me a problem! I can't use your suggested
if...else ifsince the table names are generated programmatically (they are unknown when the app starts up) and there could be 300+ of them! Looks like I have a major restructure on hand :sigh: if I really need to change the T-SQL commands into stored procs (which I've been advised to do for speed and security). -
Thanks Colin, What you say makes complete sense - but that gives me a problem! I can't use your suggested
if...else ifsince the table names are generated programmatically (they are unknown when the app starts up) and there could be 300+ of them! Looks like I have a major restructure on hand :sigh: if I really need to change the T-SQL commands into stored procs (which I've been advised to do for speed and security).chris fearnley wrote: table names are generated programmatically How often are new tables created? Just, a project I've been working on recently has about 24 tables with exactly the same structure that are put together with a view. (The reason is that each year so much data is generated that is makes sense to split it up that way and operate through the view - and since most queries only relate to one particular year it is much faster than if the records were all in the one big table.) So each year we have a script that is run that creates the new tables and updates the relevant views to include the new table. Could that work for you? (You have to be careful about the range of data in each table so that it is easy to set up constraints to get the records in the correct table when inserting - For instance in the example I mentioned above each table represents one year, so there is a field for year and all the records in a table contain the same year value - but when you look at the data through the view it appears as one continuous set of records)
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar "On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871)
-
Thanks Colin, What you say makes complete sense - but that gives me a problem! I can't use your suggested
if...else ifsince the table names are generated programmatically (they are unknown when the app starts up) and there could be 300+ of them! Looks like I have a major restructure on hand :sigh: if I really need to change the T-SQL commands into stored procs (which I've been advised to do for speed and security)."major restructure" is an understatement :-D What Colin was talking about was using the exec function... declare @whichtable varchar(50) declare @sql varchar(500) set @whichtable = 'TableA' set @sql = 'insert into ' + @whichtable + 'ect.' exec(@sql) I definitely do not recommend this. It is a performance killer. Jeff Martin Triple20 Software
-
chris fearnley wrote: table names are generated programmatically How often are new tables created? Just, a project I've been working on recently has about 24 tables with exactly the same structure that are put together with a view. (The reason is that each year so much data is generated that is makes sense to split it up that way and operate through the view - and since most queries only relate to one particular year it is much faster than if the records were all in the one big table.) So each year we have a script that is run that creates the new tables and updates the relevant views to include the new table. Could that work for you? (You have to be careful about the range of data in each table so that it is easy to set up constraints to get the records in the correct table when inserting - For instance in the example I mentioned above each table represents one year, so there is a field for year and all the records in a table contain the same year value - but when you look at the data through the view it appears as one continuous set of records)
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar "On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871)
Most of the tables are created soon after the app starts up, as external hardware devices are 'discovered' via serial ports and network ports. The table names are based on the serial numbers of these devices. Your suggestion may be useful to us in the long run (see below!) In the short term I've solved the immediate problem (I think) simply by creating a new stored proc for each table as it is created. The procs are working now anyway. If your SQL experience covers security issues, and you know what "21CFRpart11" is, I could do with your help on a paid basis, urgently. Thanks for help so far anyway :).
-
Most of the tables are created soon after the app starts up, as external hardware devices are 'discovered' via serial ports and network ports. The table names are based on the serial numbers of these devices. Your suggestion may be useful to us in the long run (see below!) In the short term I've solved the immediate problem (I think) simply by creating a new stored proc for each table as it is created. The procs are working now anyway. If your SQL experience covers security issues, and you know what "21CFRpart11" is, I could do with your help on a paid basis, urgently. Thanks for help so far anyway :).
chris fearnley wrote: The procs are working now anyway. Excellent. I have actually thought of another possible solution. It may be possible to have a combined primary key on your tables, the extra field for the primary key would be the based on the serial number you mention above. If this is too much repetition of data (I'm guessing the serial numbers are quite long) you can set up a lookup table that contains all the relevant serial numbers and use its primary key in the main (original) table. For example:
+----+--------------+
| PK | SerialId |
| | SerialNumber |
+----+--------------+
|
|
/|\
+--------+----------------+
| PK, FK | SerialId |
| PK | OriginalId |
| | OriginalField1 |
| | OriginalField2 |
+--------+----------------+With this schema your stored procedures would only need to add an extra item to the
whereclause to get the same effect has lots of tables with identical schema. chris fearnley wrote: If your SQL experience covers security issues, and you know what "21CFRpart11" is, I could do with your help on a paid basis Unfortunately my knowledge of Security is about average for a developer (in other words: quite limited), although I am due to go on some courses later this year. However, this doesn't help your current situation. Thanks for the offer anyway.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar "On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871)