| Author |
Topic |
|
sumo
Starting Member
45 Posts |
 Posted - 2003-02-17 : 09:10:35
|
I get an error with the following type of query that states that @id is not declared. Is there a way to use a local variable in an execute statement?DECLARE @SQL varchar(2000)DECLARE @id intSET @SQL = 'SELECT @id = [id] FROM SOMEWHERE WHERE THIS = THAT'EXEC (@SQL) Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-02-17 : 09:17:40
|
| try using sp_executesql ... it allows input/output variablesEXEC sp_executesql @SQL, '@id INT OUTPUT', @id OUTPUT |
 |
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-19 : 13:44:14
|
Thank you very much! Works like a charm!quote:
try using sp_executesql ... it allows input/output variablesEXEC sp_executesql @SQL, '@id INT OUTPUT', @id OUTPUT
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-20 : 15:13:58
|
Would there be any performance hit on passing in a table variable that contained 300,000 rows compared to just using a temp table in the dynamic SQL?quote:
try using sp_executesql ... it allows input/output variablesEXEC sp_executesql @SQL, '@id INT OUTPUT', @id OUTPUT
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-02-20 : 16:05:08
|
| as far as I know you can't pass a table variable anywhere ... if you are relying on dynamic sql too much, you are doing something wrong or the application is designed poorly ... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-20 : 16:27:35
|
| What is the purpose behind what you're trying to do?Just Curious. For example, how would you get 300,000 rows in to a table datatype in the first place?Brett8-) |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-21 : 07:56:17
|
Not really. We're trying to load an old catalog that contains about 45 million different vehicle applications into a new catalog format for faster querying through a web service. We can't get around using cursors and we can't get around using dynamic SQL. We just need the fastest way possible to deal with larger data sets.quote:
as far as I know you can't pass a table variable anywhere ... if you are relying on dynamic sql too much, you are doing something wrong or the application is designed poorly ...
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-21 : 08:00:31
|
declare @this table ( field1 int)insert into @thisselect field1 from dbo.that Works the same way a temporary table does, but its scope is limited from what I understand now... :)quote:
Just Curious. For example, how would you get 300,000 rows in to a table datatype in the first place?
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-02-21 : 08:04:29
|
| dynamic SQL and cursors are not going to be the fastest way ... well it doesn't hurt to use them when you have to extract data into another table ... as long as this is a background process that can take a long time or is a one time thing. Personally I found I could write every import statement from a recent re-hash of an application with set-based importing/updating ... I didn't have to use one cursor or one peice of dynamic SQL ... so if you really care, you can give us some DDL and sample data to work with and I'm sure we could make you a hero :) |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-21 : 08:27:43
|
I need to be a hero right about now, but I'm not sure if I can share code or even our data structure for our new catalog. And yes, this is hopefully a one-time thing. It'd probably help if it had more resources than two 1.8GHz processors and 1GB of RAM, too...I'll see if I can dig something up to show you. It's difficult to explain the design without having been involved in the project from the beginning.. :)quote:
dynamic SQL and cursors are not going to be the fastest way ... well it doesn't hurt to use them when you have to extract data into another table ... as long as this is a background process that can take a long time or is a one time thing. Personally I found I could write every import statement from a recent re-hash of an application with set-based importing/updating ... I didn't have to use one cursor or one peice of dynamic SQL ... so if you really care, you can give us some DDL and sample data to work with and I'm sure we could make you a hero :)
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-21 : 09:14:56
|
Can table variables be passed into stored procedures or would it be better to use a global temp table? In our procedure, the temp tables we use are dropped and recreated for each iteration through the data. I do see a way to complicate the stored procedure we're running, but eliminate the use of dynamic SQL.quote:
dynamic SQL and cursors are not going to be the fastest way ... well it doesn't hurt to use them when you have to extract data into another table ... as long as this is a background process that can take a long time or is a one time thing. Personally I found I could write every import statement from a recent re-hash of an application with set-based importing/updating ... I didn't have to use one cursor or one peice of dynamic SQL ... so if you really care, you can give us some DDL and sample data to work with and I'm sure we could make you a hero :)
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-21 : 10:51:34
|
| Table variables can't be passed. You'd have to use temp tables if you have more than one procedure manipulating them. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-02-21 : 12:45:20
|
| wait you just said that its going to be a one time thing ... well what's up with the stored procedures? just write a single import script that does it all ... if you need chunks of code that repeat maybe use a label here or there ... I don't see any advantage to creating a stored procedure that is going to be run once or a set of them that are going to run once ... |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-21 : 12:50:26
|
Actually, the process was originally in a VB6 program and we felt it'd run faster as one large stored procedure with the necessary cursors to iterate through the data. However, when we hit groups of applications that are in the hundreds of thousands or millions, it runs very slowly.Would it be faster to do the looping in VB? The querying that is performed each iteration is actually pretty fast. It just seems like it gets bogged down when it has to load and loop through a large cursor. And, each iteration of that large cursor, it is performing single-row inserts into a table.It's a real crappy process..  quote:
wait you just said that its going to be a one time thing ... well what's up with the stored procedures? just write a single import script that does it all ... if you need chunks of code that repeat maybe use a label here or there ... I don't see any advantage to creating a stored procedure that is going to be run once or a set of them that are going to run once ...
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-24 : 11:33:49
|
Actually, I'm wrong. It will be a process that will need to run once a month.. quote:
wait you just said that its going to be a one time thing ... well what's up with the stored procedures? just write a single import script that does it all ... if you need chunks of code that repeat maybe use a label here or there ... I don't see any advantage to creating a stored procedure that is going to be run once or a set of them that are going to run once ...
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
kaillee
Starting Member
1 Post |
Posted - 2003-02-27 : 22:16:43
|
quote:
wait you just said that its going to be a one time thing ... well what's up with the stored procedures? just write a single import script that does it all ... if you need chunks of code that repeat maybe use a label here or there ... I don't see any advantage to creating a stored procedure that is going to be run once or a set of them that are going to run once ...
Dear Onamuji,Do you mind to give some example here? because a i have similar probelm with sumo, because my application is depends on user login to which database, so i have to write dynamic sqlBest regardsHerbert |
|
|
|
Artanis
Starting Member
5 Posts |
Posted - 2003-03-05 : 13:27:16
|
Hi there SUMO,From the Very Beginning:I'd write that first code asDECLARE @ID IntSELECT @ID = [id] FROM WHERE THIS = 'THAT' quote:
Not really. We're trying to load an old catalog that contains about 45 million different vehicle applications into a new catalog format for faster querying through a web service. We can't get around using cursors and we can't get around using dynamic SQL. We just need the fastest way possible to deal with larger data sets.
If you're trying to insert a large dataset into another table.. and if the tables are something like the same format, you can use the followind statement:INSERT INTO Table1 VALUES(SELECT * from OtherTable) In this case, as far as I know, you must make sure the data types are the same. If everything is Right, the Insert will be done once for all, from OtherTable to Table1.As you're going to run this process once a month, you can schedule a Stored Procedure to do this stuff every time you need or, if you're more confortable with, write up a VB program that starts your stored procedure.. Let's make clear that it's not the VB program that will perform the import/export task, but only "turn the process on" that will be performed by the SP in the SQL Server..I'm not sure if this statement works if you select determined fields likeINSERT INTO (Field1, Field4) VALUES(SELECT Fld2, Fld3 FROM OtherTable) But I think it worth you to try a shot...I'm back from a 5 day party vacation that we have in Brazil called Carnaval... it's 5 days of kissing women (in my case) and a lot of beer (4 everyone) ... We started to work after lunchtime today.. so please... I hope you all sorry me if I'm not understanding the real problem and if my english is looking like sh** roday... oh.. and also hope I could helpArtaniss.z.artanis@bol.com.br+55 (12)9702-7809 |
|
|
|
|
Use @ variable in executed SQL statement