May 13, 2010 at 9:05 am
I think that T-SQL could use something similar to HTML's #include server side include directive for inserting repetitively used blocks of code like error handling, auditing, or variable declarations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 13, 2010 at 10:16 am
konstanddp (5/13/2010)
Hi allhope this discussion is still active.
I am sitting with exactly the scenario where I would have liked to be able to call a subroutine within a sql script.
I am building a script that imports a csv file from a client that we use to update our "master" data. Now after many changes to the format of the file, we were instructed that we will get a file from every manager containing their data, meaning the process must be repeated a few times.
At the beginning how ever an update statement is executed that to make all data inactive so that only data in the new files are left active after the import.
In the .Net world the ideal would be to do this:
sub main()
SubUpdate()
SubImport("c:\file1.csv",....)
SubImport("c:\file2.csv",....)
SubImport("c:\file3.csv",....)
end sub
and then followed by the two subs...
The fact is that the two subroutines will never live alone as an independant stored procedure and will always be called in conjunction. can anyone tell me how I do this in SQL?
In my opinion SSIS is the best tool within the MS SQL Server environment to do what you're talking about. While you can manipulate external files with T-SQL, that's not a core strength of T-SQL. File manipulation, importation, logic and process are core strengths of SSIS.
May 13, 2010 at 10:27 am
When working with Microsoft development tools, if you find yourself struggling or spending a great deal of time implementing some repetitive task, then you're probably just doing it wrong and there is a better way to approach it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 13, 2010 at 10:16 pm
Just some peanuts from my section of the gallery. I'm not sure how I vote yet.
I think we all agree that it's a good thing to be able to write and debug a piece of code, then reuse it multiple times without having to cut-and-paste it.
Currently within SQL you can reuse code by creating stored procedures and functions (whether user-defined, table-valued, or CLR). The downside is that those objects must be created and maintained separately. But while it would be nice to be able to encapsulate code within a procedure or function, if something is worth reusing, it's probably worth reusing across a number of different objects and therefore should be an object in the DB.
You can also reuse code by building dynamic SQL, in lieu of an #include, but that raises other issues. However, this is how I handle a problem very similar to the one posed by konstanddp. But it's important to note that his issue is importing files, not working with tables.
The problem I have with the subroutine idea is that it feels like one more procedural crutch. It feels like it could be subject to the same abuse that the availability of cursors created.
Like I say, I have mixed opinions about this, and would really like to see some other concrete examples of code that would be improved by the availabilty of subroutines in T-SQL.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 14, 2010 at 3:07 am
Completely agree with eric_russell, I think there is no comparison between coding on GUI and on database, there are so many features to handle things same as you we do in GUI coding may be better ways i think.
Nag.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 14, 2010 at 9:27 am
I started this question while ago, I am glad everyone contributing to it and I appreciate everyone's opinion and view.
When writing TSQL, it is not matter of we struggle to find how to do it, that is different topic. But, when we use TSQL commands, we wish what if we have that... looking for subroutine is more like it.
We have to see how subroutines are used. Sub routines goes long way back the time of BASIC language scripting. Still vb script use it, sometimes other languages call it as function inside their program files, not outside of the program file. It is useful for reading and organizing your code. Moreover, it is like a TSQL function but stays withing your stored procedure or function. I create a small function inside my store procedure, but It may not be useful for others, so why bother creating as a separate function or procedure?
Subroutines behaves like a function, goto statement does not.. Goto statement take you to some part of the code but not necessary comes back. But, subroutines does. Sub routines does not need parameters and does not have to return any. Subroutines uses your exiting declared variable. And so and so on, as it sounds it is sub routine inside your routines of code.
Subroutine is my wish list.. but I can live without it. But, my question is what if Microsoft provide subroutines at TSQL.. may be it is not ANSI-STANDARD, I don't know
There are so many languages and scripts uses many useful commands, and appreciated by developers all over the world. Very famous one is FOR .. NEXT loop. When I was a kid, I developed using dBASE, If newer version comes, first thing I look for is ... What are the new commands will make my development easier and faster.. that is just me.
I think sub routines are nice to have.. I believe it will improve stored procedure and function development... If not, it is OK.
May 14, 2010 at 2:07 pm
Sihaab, thanks for posing an interesting topic. This was thought-provoking for me. One thing I'm curious about is how the optimizer would be expected to estimate the workload for a subroutine. We may never know, but this was a pleasant departure from the usual questions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 15, 2010 at 6:41 am
The Dixie Flatline (5/14/2010)
Sihaab, thanks for posing an interesting topic. This was thought-provoking for me. One thing I'm curious about is how the optimizer would be expected to estimate the workload for a subroutine.
Probably in much the same way it estimates the cost of a (multi-statement or scalar) TVF, SQLCLR routine, or a pass-through remote query...it would guess. The current default guess is 10,000 rows.
The wider question is an interesting one. I remember being asked once why we can create temporary stored procedures (local or global) but not temporary functions. The real answer to that is not at all interesting, but the concept is.
Passing table-valued parameters around (2008 only) is a partial solution - but the data structure is READONLY inside the receiving procedure, which rather limits its usefulness.
One possible way to workaround the general limitation is by using SQLCLR routines.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 17, 2010 at 9:51 am
Paul White NZ (5/15/2010)
...
The wider question is an interesting one. I remember being asked once why we can create temporary stored procedures (local or global) but not temporary functions. The real answer to that is not at all interesting, but the concept is.
...
Just out of curiosity, has anyone here ever developed an application that utilized temporary stored procedures? What created the #SP, another SP? What type of problem did it solve for you?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 17, 2010 at 10:38 am
In my experience, I never thought of temporary stored procedure at all. I have scripts stored my local project folders tones of them. I only create stored procedure, when I think that I have to use that again and again in a short period of time.
May 17, 2010 at 11:03 am
sihaab (5/17/2010)
In my experience, I never thought of temporary stored procedure at all. I have scripts stored my local project folders tones of them. I only create stored procedure, when I think that I have to use that again and again in a short period of time.
Now that I think about it, I actually did work on an internally developed application a few years back which allowed people in accounting to select tables and columns from drop down list boxes so they could build custom queries. The app would dynamically build the SQL statement and then create a stored procedure in a database set aside for reporting. It wasn't really temporary procedure as in # or ##, but just ordinary procedures that the reporting users created on demand when needed. They would use Excel, Crystal Reports, or SAS to report off of the procedures, and could later use the dashboard app to drop them when not needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 17, 2010 at 12:28 pm
eric_russell 13013 (5/17/2010)
Paul White NZ (5/15/2010)
...
The wider question is an interesting one. I remember being asked once why we can create temporary stored procedures (local or global) but not temporary functions. The real answer to that is not at all interesting, but the concept is.
...
Just out of curiosity, has anyone here ever developed an application that utilized temporary stored procedures? What created the #SP, another SP? What type of problem did it solve for you?
I used them to overcome the N4k and C8K barriers in SQL Server 2000.... and, yep... it was from another stored procedure.
--Jeff Moden
May 17, 2010 at 2:13 pm
Jeff Moden (5/17/2010)
...
...
I used them to overcome the N4k and C8K barriers in SQL Server 2000.... and, yep... it was from another stored procedure.
Basically, how did creating a temporary stored procedure on the fly help work around an issue caused by varchar length limitations in SQL Server 2000 ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 3, 2011 at 12:24 am
There can be any number of reasons to call a bit of code repeatedly. If that code is very simple and can easily fit into a UDF and perhaps even be useful elsewhere, great, put it in a UDF. But if that code operates on lots of existing data or variables that you are using within the current sp, you will have to pass all that to the new sp you create to substitute for a subroutine. That can be pretty frustrating. As for using GOTO, that's just a disaster waiting to happen when it doesn't follow the path you expected it to. Using GOTO's is definitely not a good replacement for a subroutine structure.
All IMHO of course.
March 3, 2011 at 6:32 am
Eric M Russell (5/17/2010)
Jeff Moden (5/17/2010)
...
...
I used them to overcome the N4k and C8K barriers in SQL Server 2000.... and, yep... it was from another stored procedure.
Basically, how did creating a temporary stored procedure on the fly help work around an issue caused by varchar length limitations in SQL Server 2000 ?
Oh my. My apologies, Eric. I somehow missed this one a while back and it came to the surface again after Telarian posted above.
The way I did it was to create a Temporary Stored Procedure using dynamic SQL. I forget what the T-Sproc had to do because it was a while back but I basically built the T-Sproc by populating several 8K variables and then I executed them using EXEC (@Var1+@Var2+@Var3). The reason I did it is because I needed to execute the functionality many times in the same stored proc with different parameters. IIR, it was to support some nasty ol' 3rd party software that created a new table every day with a date oriented name. The external stored proc would receive the table name as a parameter. Looking back at it, I don't remember the details as to why it couldn't have been done with just regular dynamic SQL except that I do remember that sp_ExecuteSQL would have been involved and it wouldn't take large enough dynamic SQL.
Of course, there is no 4k or 8k barrier to sp_ExecuteSQL anymore but there was in SQL Server 2000. Here's some sample code that shows how I did it. In this case, the total number of characters for the sproc doesn't come close to overrunning the 8k barrier but the technique in the code below will definitely allow for it. The reason why most people never tried it is because most think the scope of it all won't work. Surprise, surprise! It works just fine. 🙂
The following code builds a Temporary Stored Procedure using a variable for the table name and then executes the T-Sproc passing a parameter as you would with any sproc and getting a return from the return variable.
--===== How to build and use a simple temporary stored procedure
-- Jeff Moden
--===== If temporary proc exists, drop it
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP PROCEDURE #MyHead
;
--===== Declare the local variables
DECLARE @TmpProcReturn INT, --Holds the return from the temp proc
@SQL1 VARCHAR(8000), --Holds the dynamic SQL
@SQL2 VARCHAR(8000), --Holds the dynamic SQL
@SQL3 VARCHAR(8000), --Holds the dynamic SQL
@pTableName VARCHAR(128)
;
--===== Presets
SELECT @pTableName = 'sys.Objects',
@SQL1 = '
CREATE PROCEDURE #MyHead
@SomeInt INT
AS',
@SQL2 ='
--===== Declare the internal return variable
DECLARE @Return INT;',
@SQL3 ='
--===== Set the return variable to some number
SELECT @Return = 25
--===== Select some stuff directly from the table just to prove it all worked
SELECT TOP (@SomeInt) *
FROM ' + @pTableName + ';
SELECT @Return = ISNULL(@Return,0);
RETURN @Return;'
--===== Execute the dynamic SQL to create the temp proc
EXEC (@SQL1+@SQL2+@SQL3)
--===== Execute the new temp proc, get it's return, and select it
EXEC @TmpProcReturn = #MyHead 5
SELECT @TmpProcReturn
--Jeff Moden
Viewing 15 posts - 31 through 45 (of 96 total)
You must be logged in to reply to this topic. Login to reply