FAQ: SQL Server stored procedure how-tos

Write and run SQL Server stored procedures more effectively. This collection of FAQs will start you on the path to working with useful T-SQL routines.

  SQL Server stored procedures are collections of Transact-SQL (T-SQL) query statements that run inside the database

server, allowing you to do your routine tasks faster. You can find a collection of handy stored procedures in this tips section, but if you're looking for some basic how-tos for writing and working with stored procedures this FAQ is the place to start.

Frequently Asked Questions:

SQL Server stored procedure how-tos

  1. Can I write stored procedures without knowing T-SQL?
  2. How would I compare T-SQL and CLR routines?
  3. Can I query a stored procedure again?
  4. Can I use a stored procedure to copy views?
  5. How can I tell when a stored procedure was modified?
  6. Can I run a stored procedure in a master database?
  7. How can I import data using a stored procedure?
  8. Can I use a routine to view tables in another database?
  9. How can I use results from one stored procedure in another?
  10. Can I execute an Oracle stored procedure on SQL Server?

  1. Can I write stored procedures without knowing T-SQL?

As a result of Microsoft's ambitious marketing of SQL Server 2005's .NET integration over the last few years, many developers believe that T-SQL will no longer be necessary to create SQL Server stored procedures. Unfortunately (or not, depending on your point of view), this is only partially true. While it is technically possible to create a stored procedure without using T-SQL, it is not possible to do any data access without T-SQL.

Data access within CLR stored procedures is done using the standard ADO.NET classes. Developers will find that much of the same data access code usable in application tiers will be easily portable into SQLCLR routines. As these ADO.NET classes in the middle tier require T-SQL for data access, so do the same classes used within the context of the hosted CLR provider.

I noted that it is technically possible to write a T-SQL-less stored procedure. So is there any reason to do so? One case for this is a CLR stored procedure written to retrieve data from a flat file or Web service and format it into a rowset. That would be an operation that would not require T-SQL – but it's not a good comparison with the abilities of T-SQL stored procedures.
—Adam Machanic, SQL Server 2005 Expert

Return to stored procedures FAQs

  2. How would I compare T-SQL and CLR routines?

Here is the general performance rule when comparing T-SQL routines to equivalent CLR routines: Test both configurations with your data on your servers and figure out which one is better.

That said, many people have run performance tests and the general consensus is that T-SQL will always perform better for standard CRUD (Create, Read, Update, Delete) operations, whereas CLR code will perform better for complex math, string manipulation and other tasks that go beyond data access.

SQL Server MVP Gustavo Larriera has compiled the following list of useful links with more information on this topic:


—Adam Machanic, SQL Server 2005 Expert

Return to stored procedures FAQs

  3. Can I query a stored procedure again?

Each execution of a stored procedure is unrelated to any other execution of that procedure. Requerying a procedure is then just another execution of it.
—Greg Low, Development Expert

Return to stored procedures FAQs

  4. Can I use a stored procedure to copy views?

I need to copy a simple view in one database on one server to a table in a separate database on a separate server nightly. I would like to do this with a stored procedure on the server where the view lives. I can drop the target table and just recreate it.

What is the easiest way to accomplish this? Can/should I do this from a stored procedure or should I use DTS or BCP?

Both machines are running version 8. One server is local, the other is accessed via TCP and lives in a data center off site. The size of the view can become quite large over time. But the copy would be done nightly with no users online. Can you give me some advice on direction here? 

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose. In this task you specify the source and destination servers, the source and destination databases along with the object(s) you wish to transfer. You will have to specify the exact database object(s) you wish to transfer in the copy tab of this task.

Create the DTS Package and verify that you can execute it in DTS Design Mode. Verify the view was indeed copied to the remote server. You may have to drop the destination object each time the package is executed. Save the package and then you can have the SQL Server Job scheduler automatically kick-start the package by right-clicking on the newly created package and choosing Schedule Package option. From here, you can provide the job scheduling details.

One additional point I'd like to make. I'm confused by your stating that the size of the view can be quite large over time. Views, unlike tables, do not hold data. They are simply named SQL statements we create to perhaps simplify the end-users perception of data in our database. I've also used views for security purposes to hide or exclude sensitive columns. Views, therefore, are not large because they are simply database objects that hold SQL Statements. Granted, the table(s) that view 'hits' can certainly be large.
—Joe Toscano, Integration Services/Development Expert

Reader's follow-up question

What I'm actually trying to do is to create a table on the target server based on the view and the data portrayed in the view. In other words I need to use the structure of the view in one server to create a real table in the target server, then populate it with the data that the view was created to portray on the source server.

I'm looking for the best solution to this scenario. I was hoping there was such a package or procedure. I'm actually integrating a normalized database with five tables into a hierarchical table structure on the target server. I use a union view on the source server to get the data looking right, i.e. matching the structure of the target hierarchical table on the target server. Then I need to drop or truncate the hierarchical table on the target server nightly and re-populate it from the view. The source server is local to me, the target server is remote hosted in a datter. The data transfer is via TCP/IP. 

You have a view on a source server. On the target server you need to create a table based on the schema of this view and you need to populate this new table with all of the corresponding source data. Finally, you need to be done on a daily basis.

Here are a couple options you may want look into:

Option 1: Use a linked server

First, you can create a linked server on your source server that points to the target server. Look under the security tab in Enterprise Manager so do this. Once this is done, you can access the target servers' database objects using the fully qualified path name as follows:

TARGETSERVERNAME.databasename.dbo.targettablename

If you choose to go this route, you can create and schedule a SQL Server Job on the source server that has the following Transact-SQL Steps:

Step 1: Drop table TARGETSERVER.databasename.dbo.targettable

Step 2: Select * into TARGETSERVER.databasename.dbo.targettable

From database.dbo.v_vewname

Here, the select into will automatically create the new table every time the job is executed. The schema of this newly created table will match the result set produced by selecting from the view. You can place these Transact-SQL Steps in a stored procedure that resides on the source server if you'd like and have a job automatically execute the procedure on a scheduled basis.

Here's a similar approach that also uses linked servers. The difference is that the second link-server approach assumes that you already have created the target table. This second approach uses a insert / select which is a logged statement while the first uses a 'select into,' which is non-logged. The nonlogged statement should run real fast while the insert /select approach is logged and will be slower.

Step 1: truncate table TARGETSERVER.databasename.dbo.targettable

Step 2: insert TARGETSERVER.databasename.dbo.targettable (column listing)

Select column listing ... from database.dbo.v_viewname

Option 2: Use a DTS package with a data pump

If you don't want to create a linked server definition, you can create a DTS Package that accomplishes the same thing. The DTS Package should have two connections that I will call SOURCESERVER and TARGETSERVER. It is assumed that you will manually create the target table on the target server. The DTS Package then must perform the following steps:

  1. Truncate the targettable on the TARGETSERVER. This step must be done BEFORE the data pump step.
  2. Create a datapump step that specifies the SOURCESSERVER as the source and the TARGETSERVER as the destination.
  3. Configure the Data Pump as follows:
    • The actual source specified in the data pump definition will be the source servers's view
    • The actual destination specified in the data pump definition should be the target server's target table.
    • Click on the transformations tab to verify that the source view columns and the destination target table columns map correctly.


—Joe Toscano, Integration Services/Development Expert

Return to stored procedures FAQs

  5. How can I tell when a stored procedure was modified?

 

In all SQL Server databases, both system and user defined, a table by the name of sysobjects stores the creation date among other data for all objects in the database. Unfortunately, the only value that is captured in any of system tables with respect to object dates is sysobjects.dbo.crdate, but not the last date/time modified that you are searching for. This date would be very beneficial to validate a new stored procedure has been released, but the sysobjects.dbo.crdate value is not updated. One way to have SQL Server reflect the updates is to not ALTER (PROCEDURE) any stored procedures, which occurs by default when using Enterprise Manager, but rather script out the stored procedures and execute the code in Query Analyzer with a DROP PROCEDURE and a CREATE PROCEDURE statement each time an object is released. This way the value in sysobjects.dbo.crdate will reflect the date/time stamp for the latest changes.
—Greg Robidoux, Backup and Recovery Expert

Return to stored procedures FAQs

  6. Can I run a stored procedure in a master database?

To access objects in the master database, you can specify the three part name for the objects eg: master.dbo.someobject from a proc in any database.
—Greg Low, Development Expert

Return to stored procedures FAQs

  7. How can I import data using a stored procedure?

Absolutely! I've written many stored procedures to import data from other servers, usually Oracle and Access as well as from text files. When importing data from other servers, you'll have an easier time if you set up the source server as a Linked Server (see SQL Server Books Online for more details).

Once you've set up the Linked Server, you can write SQL statements directly against the Linked Server. For example, you could write the statement INSERT INTO local_table…SELECT col1, col2,… FROM linked_server.db1.dbo.foo.

When importing data from text files, simply use the BULK INSERT statement or, my preference, call the BCP command line utility using XP_CMDSHELL within the stored procedure. It takes a little work to get the exact XP_CMDSHELL string correct. All you have to do is encapsulate the BCP command-line string within a parameter for XP_CMDSHELL extended stored procedure.
—Kevin Kline, SQL Server Expert

Return to stored procedures FAQs

  8. Can I use a routine to view tables in another database?

Yes, it certainly is possible, easy in fact. The way you reference data in a separate database is with a three-part name. The three parts are the database name, the owner or schema name and the table name. So if your stored procedure is running in the northwind database and you need to access the authors table in the Pubs database you would refer to it as pubs.dbo.authors. Here's an example in a stored procedure:

 

 use northwind go CREATE PROCEDURE dbo.usp_Example_CrossDatabase AS select * from pubs.dbo.authors go EXEC dbo.usp_Example_CrossDatabase GO


—Andrew Novick

Return to stored procedures FAQs

  9. How can I use results from one stored procedure in another?

So long as the stored procedure produces only a single result, the technique for using the output of one stored procedure in another is pretty straightforward. The technique is to use a temporary table to hold the results of the stored procedure and an INSERT EXEC statement to execute the sproc and save the results. Once the results are in the temporary table they can be used like any other table data. Here's an example procedure that we might like to reuse:

 

 CREATE PROC usp_Demo_AllAuthors as select * from pubs..authors GO

Now here's a stored procedure that uses the results of usp_Demo_AllAuthors:

 

 CREATE proc usp_Demo_SPUser as CREATE TABLE #Authors ( au_id varchar(11) NOT NULL PRIMARY KEY CLUSTERED, au_lname varchar (40) NOT NULL , au_fname varchar (20) NOT NULL , phone char (12) NOT NULL, address varchar (40) NULL , city varchar (20) NULL , state char (2) NULL , zip char (5) NULL , contract bit NOT NULL ) -- Execute usp_Demo_AllAuthors storing the -- results in #Authors insert into #Authors exec usp_Demo_AllAuthors -- Here we use the #Authors table. This example only -- only selects from the temp table but you could do much -- more such as use a cursor on the table or join with -- other data. SELECT au_fName + ' ' + au_lname as [name] , address+', '+city+', '+state+' '+zip [Addr] from #Authors DROP TABLE #Authors GO


—Andrew Novick, SQL Server Expert

Return to stored procedures FAQs

  10. Can I execute an Oracle stored procedure on SQL Server?

Oracle uses its own dialect of SQL called PL/SQL (procedural language extension to Structured Query Language), while SQL Server uses T-SQL (Transact-SQL). The two have very different rules about variables, parameters, conditional processing, error handling and so forth. So unless the Oracle stored procedure contains only ANSI SQL statements, it won't work on SQL Server. However, Microsoft recently introduced a new tool that aids in the migration process from Oracle to SQL Server. You should investigate the SQL Server Migration Assistant. You should also note that moving from Oracle to SQL Server is not considered a simple point-n-click process. It usually involves a healthy dose of planning, analysis and good ol' fashion elbow grease.
—Kevin Kline, SQL Server Expert

Return to stored procedures FAQs

 

 
 
 

Didn't find what you were looking for?

Pose a question to anyone of our SQL Server experts.

You can also browse our SQL Server Topics section for more advice.

 
 
This was first published in June 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close