How to pass TABLE datatype as input parameter to a stored procedure

Question

Post reply

How to pass TABLE datatype as input parameter to a stored procedure

itrap

SSC Enthusiast

Points: 175
More actions
January 19, 2004 at 8:07 am

#152018

Hello, I am interested in passing table datatype as an input parameter to a stored procedure.
Any ideas on how this can be done??? Do I need to take a varchar tablename parameter and convert it to table datatype or is there another way?? Please help

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

The table data type cannot be used as a stored procedure parameter. Use a temporary table instead.

--Jonathan

itrap SSC Enthusiast Points: 175 More actions · Answer 2

Thanks Jonathan for the prompt response. I am new to SQL Server and TSQL, I would really appreciate if you can help me out

Here is the stored procedure:

*********************************************************************

CREATE PROCEDURE sp_GetSomeRecords

@Table varchar(30),

@variable1 varchar(8),

@variable2 varchar(10)

AS

Declare @TableName table

(

/*

Table structure

*/

&nbsp

Select * from @TableName where variable1 = @variable1 and variable2 = @variable2

GO

*********************************************************************

I am interested in passing @TableName as an input parameter

Please help

Thanks in advance

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

For that you would use the sysname data type and dynamic SQL, e.g.:

use northwind

go

CREATE PROC p_FetchDyn @Tablename sysname, @Var1 varchar(8), @Var2 varchar(10) AS

SET NOCOUNT ON

DECLARE @sql varchar(500)

SET @sql = 'SELECT * FROM ' + QUOTENAME(@Tablename) + '

WHERE CustomerID = ' + QUOTENAME(@Var1,'''') + ' AND PostalCode = ' + QUOTENAME(@Var2,'''')

EXEC(@sql)

I infer you're attempting to modularize your SQL code. This is not a good idea due to the way execution plans are cached and the way security rights are implemented. Instead avoid dynamic SQL and write procedures specific to the actual queries you will need. Also avoid using the "*" wildcard in your select lists and do not use the"sp_" prefix with your stored procedure names.

--Jonathan

itrap SSC Enthusiast Points: 175 More actions · Answer 4

Thanks Jonathan

That sure looks a lil complicated since I have not come across sysname and Quotename keywords before. I will try the code would let you know if it works ...

You asked me not to use "sp_" prefix, I have used this prefix in all the other stored procs I created and had a feeling that this was a good programming convention and has to be encouraged.

I decided to write this stored proc because I have 10 different tables all having similar structure. So instead of writing 10 sp I decided to consolidate it into one generic stored proc... was that a bad decision

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

Sysname is the data type used for SQL Server identifiers like table names and column names. By using sysname, you ensure that your code will upgrade if Microsoft changes the rules for this. Sysname in SQL 7 and 2000 is equivalent to nvarchar(128). The QUOTENAME() function is used in the first case (@Tablename) because identifiers can contain spaces and in the later cases because the values could contain quotes.

From BOL Creating Stored Procedures:

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Instead of ten tables with identical structures, your schema should probably be one table with another column identifying whatever it is that now distinguishes the tables from one another.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Piggy-backing on Jonathan, this article might explain this thing about sp_* a bit more

http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gljjr SSChampion Points: 10080 More actions · Answer 7

Using one sproc to go against 10 different tables means that you would need to use dynamic sql rather than staight sql. This means that the caching of the query plans would need to be recalculated each time the sp is run. That is extra overhead that will ultimately cause a performance problem.

As far as the sp_ goes. The only sp's I write with sp_ go into the master database as utilities. In this way I can call them from any database. I do this with for things like sp_select (you can find this in the samples here). That way no matter what db I'm in it will still work. However I would never use sp_ or just sp but instead use usp_ to prefix my database specific stored procedures. This then allows you to easily find your sp's when using the object browser while allowing system objects to be shown.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

DanaH1976 Ten Centuries Points: 1046 More actions · Answer 8

In Jonathan's post, he advised against dynamic SQL. Is this a blanket statement? Should dynamic SQL always be avoided?

Dana

Dana
Connecticut, USA
Dana

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Aah, that's my turn again

Read this http://www.sommarskog.se/dynamic_sql.html and decide for yourself which way to go

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gljjr SSChampion Points: 10080 More actions · Answer 10

Dana,

In the post that Frank points to the line that states "this is a powerful feature that you should use with care" is the one you must take to heart. Dynamic SQL can be a very powerful and useful tool in your arsenal but use it with care. Also be sure to test to make sure that the performance is up to par when ever you do use it. For things like complicated searches it can be a godsend.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

What I even like more in Erlands' article is the section about common cases and their workaround without advising to use dynamic sql. Sometimes these workarounds are amazingly trivial.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]