|
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!
<br />
CREATE PROCEDURE dbo.new_zone<br />
@whichtable varchar(20),<br />
@num int,<br />
@name varchar(20)<br />
AS INSERT @whichtable (n,nme) VALUES (@num,@name)<br />
GO<br />
|
|
|
|
|
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 if since 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  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)
|
|
|
|
|
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 where clause 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)
|
|
|
|
|
"major restructure" is an understatement 
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
|
|
|
|
|
My task is to generate a series of SELECT FROM table WHERE <where clause=""> statements and to generate the UNION of the resultant sets of each Select. My idea is to do it with the disconnected ADO.NET paradigm. One idea is to load the entire table into a DataSet and then create views using the SELECT statements as row filters. Finally, merge the views into one table. I know that the DatsSet class has a Merge method but I don't see how to apply it to views. Any clues?
Gary Hyslop
|
|
|
|
|
Perhaps instead of trying to merge the data, query all of the data and use the .RowFilter method to obtain the user's needs. It really doesn't matter if you obtain the data from a view or stored procedure, but I would think filtering existing data would be much easier that attempting to merge datasets.
dim dv as DataView = dsAllData.Tables(0).defaultView
dv.rowFilter = sFilter
Return dv Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
My problem with "querying all of the data" is that I have to save each piece and then take the union of the pieces. I want to avoid using temporary tables or subqueries and do the work in memory. An ADO.NET View object seems ideal for such a purpose. I just need to take the union of (i.e. merge) the pieces.
The Table is the same for all queries. In other word:
View = Table
Q1 = View.Filter1
Q2 = View.Filter2
...
Qn = View.Fi;tern
Results = Q1 Union Q2 ... Union Qn
Ex. n=3 Q1 + {a,b,c} Q2 = {d,c,e} Q3 = {a} Result = {a,b.c.d,e}
Gary Hyslop
|
|
|
|
|
Hi,
I want to know the row(s) in which a Time Field is = to 15h00 :
I've tried this but it doesn't work.
SELECT * FROM MyTable WHERE TimeField=#15:00# ;
have you got an idea ?
BrutalDeath0
|
|
|
|
|
Brutaldeath0 wrote:
a query suing Time format
I want to know the row(s) in which a Time Field is = to 15h00
I'm not a lawyer - but you could try for a John Doe lawsuit. 
"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)
|
|
|
|
|
Okay, it's been a long day... Here is a more serious answer....
SELECT * FROM MyTable WHERE datepart(hour, TimeField) = 15
"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)
|
|
|
|
|
why not just use the LIKE clause. LIKE '15h00'. aren't dates stored as varchars, if not then you'd need to cast somehow. im just giving this advice from a programmers perspective
|
|
|
|
|
Hello to all,
How can i lock records or a table in transaction using MS Access database and VB.NET.
My experiments shows that this is imposible.
What do you think about it?
Regards Alex
|
|
|
|
|
Yes, it is definitely possible to do transactions with MS Access. You can use either ODBC or classes from the System.Data.OleDb namespace.
My articles and software tools
|
|
|
|
|
I understand you. And I allready using .NET classes to acces the data in the MS Access database, but there are only three methods supported by this database:
1 Chaos
2 ReadCommitted
3 ReadUncommitted
and they can't give me full functionality that I need.
I need lock records in the database table or if it's imposible then lock all table.
That all I want.
Regards Alex
|
|
|
|
|
Do the ADO.Net DataSets contain any kind of support for the [binary] serialization of objects?
Ideally, I'd like to be able to set a reference to a field, as if it was a value, which has its insertion in as a string/BLOB automated.
At the moment my only solution would be to dump DataSets and use custom objects with DataReaders.
|
|
|
|
|
How to check whether SQL server is installed on my machine?
How to create a new MSDE database?
I would like to create a new MSDE database.
|
|
|
|
|
Prasad N wrote:
How to check whether SQL server is installed on my machine?
http://www.codeproject.com/csharp/servercombobox.asp[^]
Prasad N wrote:
How to create a new MSDE database?
You should run commands I think. Start with CREATE DATABASEin SQLServer online boook.
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
i am recently working on a project regarding database conversion. i am facing a problem in converting the stored prodcedures of oracle to sql server in vb.net or by using ado.net, can someone help me out in this matter.
thanks.
|
|
|
|
|
If you have a PL/SQL Stored Procedure, your best bet would be to convert it to a T-SQL stored procedure. Have a look at the SQL Server Books On Line for full details.
|
|
|
|
|
Is there a way to call a column by index rather than by its name. I'd like to crawl to a specific column, as in:
SELECT {column + 2} FROM Table
Similar to how you would refer to an element in a C++ array using pointer arithmetic.
|
|
|
|
|
Simple answer: no.
Complicated answer: if the column was called Coln (e.g. Col2) you could probably use sp_executesql (preferred) or EXEC to construct some dynamic SQL.
SQL is very good in the vertical direction, finding rows which match particular criteria; it's very weak in the horizontal.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
OK, I see what you mean. It's basically the way SQL works. I guess I'll just have to use a bunch of IF statements and choose the column based on the index I give it. It's not a big deal, I was just in a more dynamic frame of thought.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
