Weird ass SQL stored procedure question...

[Kratz]

This has changed significantly since I started writing it... so I had to retitle.

I write a new stored procedure to insert data into a new table I've created, and when I call the stored procedure in my ASP code it is obviously connecting to the database, because it knows the table that is referenced in the stored procedure, but I'm getting an error that says my table is an invalid object. If I view dependencies on the SP, it doesn't show the table.

It seems that if IN the SP I explicitly list the column names I want to use, it CAN see the table in the dependencies, but if I do it the way I *want* to do it, with a dynamically generated list of column names and values (which works just fine if I process the query right in the ASP code), it can't see the table, and I think this is my problem...

I'm not as SQL Server savvy as I should be, so maybe I'm trying to do something I just can't do, but if someone knows... I'm basically trying to do this:

Code: Select all

CREATE PROCEDURE dbo.PDF_Submission_SP
 @FieldNames NVARCHAR,
 @FieldValues NVARCHAR
 AS

insert into z_FormDataTable (@FieldNames)
values (@FieldValues)
GO

Is this a no-no?

[Croaker24]

I'm more of an Oracle programmer than SQL Server, but no, this does not look legit.

You'll need to resort to some form of dynamic SQL, something along the lines of:

CREATE PROCEDURE dbo.PDF_Submission_SP
@FieldNames NVARCHAR,
@FieldValues NVARCHAR
AS

DECLARE STMT VarChar(500)
SELECT @STMT = 'INSERT INTO Z_FORMSDATATABLE ( '
SELECT @STMT = @FIELDNAMES + ' )'
SELECT @STMT = ' VALUES (' + @FieldValues + ')'
EXEC (@STMT)
GO

That should get you started I hope.

[Quaro]

What he said. Syntax might be slightly different in SQL server, it's been too long for me to remember exactly. Dynamic SQL basically means you build a string and then execute that string as a command.

[Kratz]

That's what I've done in the past... right in code. Then if we wanted it in stored procedures we just had a SQL monkey who was good at it do that part for us... because we were lazy.

Looks like I really need to list all field names and pass values for each of them... so my nice little flexible function will turn into a monster, but oh well.

[Croaker24]

Another thing -- dynamic SQL can be bad in a web environment due to the parsing overhead and lack of bind variables, and lack of caching in the shared pool.

If this is something that is executed frequenty, particularly concurrently, you could really slog down the database.

[Kratz]

No, very infrequently... like at maximum... 100 - 200 in a month.

[Det. Dave]

The query optimizer is trying to compile your sql statement and is choking on the @fieldname variable.

You can use an exec statement which is shoddy programming if you want to, but you should just suck it up and add a parameter for every field in the table.

Then write some asp code to create a complete set of name/values for each column with the default values and then loop through your dynamic values replacing the defaults.

If this is too much for you, the just use the dynamically generated sql statement instead of the stored proc.

Alternately, You could also insert an "empty record" and then issue a series of update statements using the primary key that is returned (works best if you are using an identity field and can return @@identity as a return value from the proc that inserts a blank record. Then you can loop through your name value pairs calling an update proc for each pair. This approach would make sense if you have a table with a LOT of columns and a variable set of values to insert.

[Kratz]

You can use an exec statement which is shoddy programming if you want to, but you should just suck it up and add a parameter for every field in the table.

Then write some asp code to create a complete set of name/values for each column with the default values and then loop through your dynamic values replacing the defaults.

This is exactly what I ended up doing... with a 167 column table (and no, I did not design the table). It was good fun.