few queries in Stored procedures
-
Q.1 what is function of GO in SP's? Q.2 If we have to use nested queries then how far we can go i.e. Select * from where ( select id from where (........IS THERE ANY LIMIT HERE TO USE NESTED LOOPS..........)))))) OTHER QUERIES I'LL POST LATER THANKS :) IN ADVANCE FOR GIVING THE ANSWERS PARAG
-
Q.1 what is function of GO in SP's? Q.2 If we have to use nested queries then how far we can go i.e. Select * from where ( select id from where (........IS THERE ANY LIMIT HERE TO USE NESTED LOOPS..........)))))) OTHER QUERIES I'LL POST LATER THANKS :) IN ADVANCE FOR GIVING THE ANSWERS PARAG
GO signifies that the current batch of Transact-SQL statements should be sent to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. GO must be on a line of its own but may include comments. Any number of sub-queries may be nested in a statement. Steve
-
Q.1 what is function of GO in SP's? Q.2 If we have to use nested queries then how far we can go i.e. Select * from where ( select id from where (........IS THERE ANY LIMIT HERE TO USE NESTED LOOPS..........)))))) OTHER QUERIES I'LL POST LATER THANKS :) IN ADVANCE FOR GIVING THE ANSWERS PARAG
ParagGupta wrote:
Q.1 what is function of GO in SP's?
In contrast to what the other poster has said GO has nothing to do with stored procedures. It is simply a preprocessor command given in Query Analyzer (and you can set Query Analyzer to accept other things in place of GO if you prefer). It is not part of the T-SQL language. GO tells Query Analyzer where to split the script into batches. Each batch is executed on the SQL Server in sequence, but without reference to any batch that came before it in the script. In other words if you DECLARE a variable in one batch, you cannot see it in the next. With regard to Stored Procedures, there is no way to tell SQL Server that where the end of the stored procedure is. It must therefore come last in a batch and Query Analyzer will delimit the end of the stored procedure with the GO keyword.
ParagGupta wrote:
Q.2 If we have to use nested queries then how far we can go
No idea - I've gone 4 deep in one query, but if you are going that deep you might want to rethink your strategy. About 6 months after I wrote that 4-deep query I refactored it to use temporary tables instead, and the query went from taking about 20 minutes to 7 seconds. There is only so much the SQL Server query optimiser can cope with.
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
GO signifies that the current batch of Transact-SQL statements should be sent to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. GO must be on a line of its own but may include comments. Any number of sub-queries may be nested in a statement. Steve
My post regarding the GO statement was correct, however, I omitted to mention that this relates to the Query Analyzer and not stored procedures (as Colin pointed out). I apologise if that omission rendered my post misleading. Steve
-
Q.1 what is function of GO in SP's? Q.2 If we have to use nested queries then how far we can go i.e. Select * from where ( select id from where (........IS THERE ANY LIMIT HERE TO USE NESTED LOOPS..........)))))) OTHER QUERIES I'LL POST LATER THANKS :) IN ADVANCE FOR GIVING THE ANSWERS PARAG
GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer. osql Utility The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server. isql Utility The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently. A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command. SQL Server applications can send multiple Transact-SQL statements to SQL Server for execution as a batch. The statements in the batch are then compiled into a single execution plan. Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch. Applications based on the DB-Library, ODBC, or OLE DB APIs receive a syntax error if they attempt to execute a GO command. The SQL Server utilities never send a GO command to the server. GO is a utility command that requires no permissions. It can be executed by any user. Regards, Uma
-
Q.1 what is function of GO in SP's? Q.2 If we have to use nested queries then how far we can go i.e. Select * from where ( select id from where (........IS THERE ANY LIMIT HERE TO USE NESTED LOOPS..........)))))) OTHER QUERIES I'LL POST LATER THANKS :) IN ADVANCE FOR GIVING THE ANSWERS PARAG
I think upto 32 levels. But please check SQL online.... Go is used for batch submission. Amit