Help On Stored Procedure 2

[JeffSabat]

Why is this not possible?

CREATE PROCEDURE [uspInsertNotes]
@OwnerID varchar(50),
@Subject varchar(50),
@Notes ntext(16),
@DateCreated varchar(50),
@CreatedBy varchar(50),
@tableName as nvarchar(256)
AS
INSERT INTO @tableName
(OwnerId,Subject,Notes, DateCreated, CreatedBy)
VALUES
(@OwnerID, @Subject, @Notes, @DateCreated, @CreatedBy)

Is there a solution to this problem or it is really neccessary to name the table in this code...

Also, is it possible i will pass an array of data to a parameter like for example, multiple subject, multiple notes?
Pls help....
tnxs a lot..

 

[nigelrivett]

For thge array pass a csv string and parse it in the SP or pass a temp table.

As for the table name variable look at dynamic sql

declare @sql varchar(8000)

select @sql = 'insert ' + @tblname + '(col1, col2) ''myval1'',''myval2'''
exec (@sql)

look at sp_executesql for using parameters on a dynamic sql statement.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MacLeod72]

You can use dynamic SQL to enter the table name and execute the insert. I believe that this FAQ (faq183-761) explains how this is done.

--Rob

 

[JeffSabat]

thank you very much for the help..
i'm just so happy..
again, thank you...
jeffrey