brokeMyLegBiking
asked on
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
Or is the only way to make a separate procedure and pass values back and forth?
thanks, - brokeMyLegBiking
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, thanks. hm, I'll have to look into extended stored procedures. Any good resources you know of on those?
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
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
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.