Using "@Variable" as table name in SP insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using "@Variable" as table name in SP insert

Author

Topic

Niagara
Starting Member

2 Posts

Posted - 2002-07-05 : 13:54:01

Hi...

I am uploading .doc files as well as writing to a database. I am using a stored procedure to increment a File ID field only after the individual has clicked the submit button.

I am passing the table name (where the INSERT is to take place) from a dynamic list as a @Variable. In that way I can upload to multiple tables/folders from a drop down select(at least I am hoping to).

In the code example below .... "@Uploadfolder" equals both the name of the folder where the .doc files are to be uploaded and the Table Name in the database where the data is to be inserted.
---------------------------------------------------------
CREATE PROCEDURE dbo.spInsertManual

(@Title varchar(250),
@Path varchar(300),
@Category varchar(100))

AS

Declare @FileID bigint
Declare @Uploadfolder varchar(100)

SELECT @FileID =
'SELECT MAX(FileID) + 1
FROM (' + @Uploadfolder + ')'

If @FileID = NULL
BEGIN
SELECT @FileID = 1
END

INSERT INTO @Uploadfolder(FileID, Title, Path, Category)
VALUES(@FileID, @Title, @Path, @Category)
GO
----------------------------------------
I'm at a bit of a loss as to if/how to use the @Uploadfolder variable as the Table Name to insert into .... or if it can be done?

I find lots of documentation on using SELECT ... but haven't come across much on INSERT.

As a NOTE .... in the code above, the PLUS signs don't seem to show up when submitting to this forum. I used them in the "SELECT MAX(FileID) PLUS 1" and on either side of the (' Uploadfolder ') within the code.

Any guidance would be appreciated....

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-05 : 14:07:14

something like

declare @sql nvarchar(1000)
declare @FileID bigint

select @sql = 'SELECT @FileID = MAX(FileID) + 1
FROM (' + @Uploadfolder + ')'

exec sp_executesql @sql, N'@FileID bigint output', @FileID output

If @FileID is NULL
SELECT @FileID = 1

select @sql = 'INSERT INTO ' + @Uploadfolder + ' (FileID, Title, Path, Category)
VALUES(' + convert(varchar(20),@FileID) + ',''' + @Title + ''',''' + @Path + ''',''' + '''Category + ''')'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-05 : 14:41:04

Since dynamic SQL has both its plus points and minus points I highly recomend a read of the following article [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]

HTH
Jasper Smith

Niagara
Starting Member

2 Posts

Posted - 2002-07-08 : 11:56:57

Thanks to all for your input.

I was leaning towards passing table name values to SQL (using dynamic SQL) ... but have decided to code the extra pages and call multiple stored procedures (each referencing distinct table names, etc.) to do the file uploads and ID incrementation.

The dynamic sql would have worked as well ... but others seem to recommend against using it for table names as well. I usually lean towards letting the server do the work and keeping the pages down to a minimum by picking up values dynamically.

Thanks again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources