subroutines in TSQL?

Is there such a thing as subroutines in TSQL?

Or is the only way to make a separate procedure and pass values back and forth?

thanks, - brokeMyLegBiking
LVL 4
brokeMyLegBikingAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
You got it, no subroutines, no GoTo's, no extensive flow control. You can use multiple stored procedures and pass variables back and forth, but in many cases you may be better off using a non-SQL programming language. You can code whatever you need in the programming language of your choice in a DLL and install it as an Extended Stored Procedure.
0
 
jdlambert1Commented:
If you want to try a DLL, here's a simple installation method from SQL Server Books Online:

How to add an extended stored procedure (Enterprise Manager)
Expand a server group, and then expand a server.
Expand Databases, and then expand the master database.
Right-click Extended Stored Procedures, and then click New Extended Stored Procedure.
In Name, enter the name of the extended stored procedure.
In Path, enter the path of the dynamic link library that contains the extended stored procedure. Optionally, click (...) to locate the DLL containing the extended stored procedure.
0
 
brokeMyLegBikingAuthor Commented:
ok, thanks. hm, I'll have to look into extended stored procedures. Any good resources you know of on those?
0
 
jdlambert1Commented:
SQL Server books online is the first place to go for the basics on extended stored procedures, but right off you'll have to chose a programming lanuguage, and then the references for that language take over.

Create a DLL (e.g. in Visual Basic) that lets you pass in a set of variables, create code to connect to the database, pull whatever data you need, do whatever manipulations you need, and store the results in the database or pass out whatever variables you need to.

Once the DLL works in the development environment, compile it and add it to SQL Server as described above or with Query Analyzer like this:
sp_addextendedproc 'xp_hello', 'xp_hello.dll'

Then you can use your function by calling it something like this:
EXECUTE @retval = xp_hello @param1, @param2 OUTPUT
0
All Courses

From novice to tech pro — start learning today.