Problem solve Get help with specific problems with your technologies, process and projects.

Parameterize tables and columns in a stored procedure

To parameterize tables and columns in a stored procedure involves more than that of developing a straight forward SQL statement. SQL Server development expert Greg Low explains.

I am designing a stored procedure on SQL Server 2000 and it accepts an input parameter. The calling procedure passes a table field name to this procedure. The stored procedure should then calculate the sum of all the values in the this field table.

ALTER PROCEDURE CrBacSetSumFields ( @SumField Varchar(50) ) AS BEGIN declare @SumOfValues numeric Select sum(@SumField) as SumOfValues From CR_ARC_INP_ACCTNG End On parsing, it goes through without any errors. However on executing it, the following appears:
"Server: Msg 409, Level 16, State 2, Procedure CrBacSetSumFields, Line 35 The sum or average aggregate operation cannot take a varchar data type as an argument."
I'm not sure how to get it around because the table names the field as a numeric type. I would like to affirm that only the input parameter is a varchar. There is also no way to put the actual column name in the stored procedure. The column name must be taken as a parameter. Can you give me a solution?
Table and column names can't be parameterized in SQL statements this way. It is possible to do this via dynamic SQL but I'd strongly suggest you take a look at Erland Sommerskog's great info on it.

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.