Passing Table name into Stored Procedure

Last Post 12 Sep 2006 12:39 PM by xfonhe. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tcarnes
New Member
New Member

--
12 Sep 2006 09:56 AM
I am trying to create a Stored Procedure that will update respective tables based on the table name variable passed in. This is what I've tried that isn't working:

declare @Text varchar(255)
declare @Table varchar(20)
declare @ID int

set @Text = 'Submitted'
set @Table = 'status'
set @ID = 4

Insert @Table ([ID], [Text]) VALUES (@ID, @Text)

I then get the error:
Msg 1087, Level 15, State 2, Line 10
Must declare the table variable "@Table".

Now do I declare the table variable "@Table"

Terry
xfonhe
New Member
New Member

--
12 Sep 2006 10:04 AM
You declared @Table as a VARCHAR(20)

Declare @Table as a TABLE; e.g. DECLARE @Table TABLE (col1 datatype, col2 datatype, ...)
tcarnes
New Member
New Member

--
12 Sep 2006 10:11 AM
Can I get this information from the system tables?

The various supporting tables have identical column names (ID, Text), but currently the Text column is different sizes depending on the Table.

I'd like to be able to pass in the Name of the table to the SP, and then have the SP do the rest. So is there a way I can Declare @Table when all I know is its name?

Terry
xfonhe
New Member
New Member

--
12 Sep 2006 10:27 AM
What is it you are trying to accomplish? If all you want to do is insert values into a table, dynamic SQL (not very elegant but will work) is the way you need to write the query.

Example (using Northwind database):

declare @Text varchar(255),
@Table sysname,
@ID varchar(10),
@dynamicSQL varchar(8000)

set @Text = 'A string'
set @Table = 'dbo.Categories'
set @ID = 9999

set @dynamicSQL = 'insert into ' + @Table + ' (CategoryID, CategoryName) values('+@ID+','+''''+@text+''')'

exec(@dynamicSQL)

NOTE: if 'ID' column is an IDENTITY column, you will need to set IDENTITY_INSERT to 'ON'

tcarnes
New Member
New Member

--
12 Sep 2006 10:54 AM
Years past, I created an ID table (columns: [Sequence] [varchar](20), [Last] [int]) which listed a series of Sequences I was keeping track of (Customer ID's, Rep ID's, etc) and a NextID Stored Procedure that when given a Sequence would return the next ID (value of Last + 1).

I then modified the NextID SP so if I passed it a Sequence that didn't currently exist, it would create a record for the new Sequence, give it a Last of 1 and return 1 as the next ID.

I then use this combination when I am normalizing database being moved from something like Paradox to SQL Server. For example, I have an Order table with a field for Customer Service Rep. For the SQL Table I make a RepID field and a separate table named Rep. My new insert SP calls GetRepID SP which checks Rep.Name and if it finds it it returns its ID, if it doesn't find it, it calls the NextID and returns that one. I've used this procedure for normalizing several tables in the past.

Now I have a project where to normalize a large table I am creating fields StatusID, LocationID, ApplicationTypeID, etc with their associated tables: status, location, application-type, etc; all which have columns ID and Text (Text differs in size depending on need).

Now, instead of creating all the separate SPs (GetStatusID, GetLocationID, GetApplicationTypeID, etc) I want to be able to create a single SP (GetID) to which I pass the Text and Table variables and I get back the appropriate ID from the appropriate Table.

Hope that helps.
tcarnes
New Member
New Member

--
12 Sep 2006 12:10 PM
How do I get a return value if my dynamic SQL is supposed to return a value?

I've tried:

declare @Text varchar(255),
@Table sysname,
@ID int,
@dynamicSQL varchar(8000)

set @Text = 'Submitted'
set @Table = 'status'
set @dynamicSQL = 'Select [ID] FROM ' + @Table + ' WHERE Text = "' + @Text + '"'

But I can't then use

set @ID = exec (@dynamicSQL)

since this form only works if I'm using exec with a SP and not a string.

Thank you for all your help with this. Trying to execute dynamic queries within SPs is a whole new area with me.

Perhaps I'm going about this all the wrong way.

Terry
xfonhe
New Member
New Member

--
12 Sep 2006 12:24 PM
Okay.

First, you can use IDENT_CURRENT('table name') function to return the current ID value, rather than a proprietary design. Not that there's anything wrong with proprietary design per se, it is just more difficult to diagnose, and redundant when system-provided functions exist. Just something to consider.

Example (using Pubs):

declare @table sysname;
set @table = 'dbo.Categories';

select IDENT_CURRENT(@table);



xfonhe
New Member
New Member

--
12 Sep 2006 12:39 PM
Okay, so what you want to do is pass in a table name and text string and for those values return the associated ID column value(s)?

Continue on the dynamicSQL path. You could combine a CASE statement so that passing in tablename X returns X.X_ID, tablename Y returns Y.Y_ID, etc.

Example:

create proc test_proc (
@table varchar(255),
@text varchar(255)
)

as

begin

declare @dynamicSQL varchar(8000), @colName varchar(255)

select @colName = case when @table = 'X' then 'X_ID'
when @table = 'Y' then 'Y_ID'
when @table = 'Z' then 'Z_ID'
end;

set @dynamicSQL = 'select ' + @colName + ' from ' + @table + ' where [text] = ' + @text;
exec(@dynamicSQL);

end


nosepicker
New Member
New Member

--
12 Sep 2006 12:51 PM
I didn't read this entire thread, but if you want to assign a value to a variable within a dynamic SQL statement, you do this (notice that the datatype for @dynamicSQL is now nvarchar, not varchar):

declare @Text varchar(255),
@Table sysname,
@ID int,
@dynamicSQL nvarchar(4000)

set @Text = 'Submitted'
set @Table = 'status'
set @dynamicSQL = 'Select @ID = [ID] FROM ' + @Table + ' WHERE Text = "' + @Text + '"'

EXEC sp_executesql @dynamicSQL, N'@ID int OUTPUT', @ID OUTPUT
tcarnes
New Member
New Member

--
12 Sep 2006 05:16 PM
Thanks for all yhour help, xfonhe!

I've decided to go the dynamic sql route. Using the case statement means my SP will need to know the different table names that may be passed in, whereas I want to make the SP generic so that I can use it in the future for many different tables and IDs.

Thanks again for pointing me in the right direction, and for your quick replies!

Terry
tcarnes
New Member
New Member

--
12 Sep 2006 05:19 PM
Thanks for your help, nosepicker.

That was just what I needed!

Terry
xfonhe
New Member
New Member

--
13 Sep 2006 04:57 AM
Glad to help.

Remember, if you use UNICODE data typing for the dynamic SQL string (i.e. nvarchar), you are limited to 4000 character string. This will allow you to use sp_executesql however, which is more efficient for dynamic sql execution. If you need to manipulate strings longer than 4000 characters use VARCHAR. With EXECUTE() you can concatenate string variables; something that is not allowed by sp_executesql. Of course, in 2005, the advent of VARCHAR(MAX) negates the need to concatenate dynamic sql variables...
You are not authorized to post a reply.

Acceptable Use Policy