Another Stored Procedure Question...

Hi,

Is it possible whithin a Stored Procedure send the table name as a parameter?
And some Columns to?

Ex.:

CREATE Procedure Xpto

@.TableName as ?
@.ColumnName as ?
@.SomeValue as nvarchar(10)

AS

SELECT * FROM @.TableName WHERE @.ColumnName = @.SomeValue

Thanks

JPP

create procedure SelectFrom @.table sysname, @.column sysname, @.value sysname
as
exec ('select * from '+ @.table + ' where ' +@.column + ' = ''' + @.value + '''')

|||

You should protect the code above against SQL injection attacks like below:

declare @.tablename nvarchar(130), @.columnname nvarchar(130), @.sql nvarchar(4000)

set @.tablename = quotename(@.table)

set @.columnname = quotename(@.column)

set @.sql = 'select * from ' + @.tablename + ' where ' + @.columnname + ' = @.value'

exec sp_executesql @.sql, N'@.value nvarchar(4000)', @.value = @.value

However, there shouldn't be a need to write such generic stored procedures. It is not a good thing to do. You have to grant permissions to users since dynamic SQL is evaluated at run-time. So please write a stored procedure per table/feature/module that can handle the data access for you. Alternatively, you can also create views and expose the data.

|||Hi,

When I post this question I was thinking on using the solution to dynamicly update some tables with the Numers of the documents.
Ex.:

Table Numbers
NroInvoice
NroReceipt
....

Then, to update one of the numbers I dont have to write a stored procedure for each on.
Do you think this is a bad aprotch?

Thank you for your time.

JPP|||Another reason to avoid such generic stored procedures is that they cannot be optimized and do not generate a pre-compiled execution plan that would be kept for repeated execution.|||Hello...

I would not use a table to hold values like this...

Those information is already in the DB and there is no need to denormalize it. If there are propper indexes on those fields you want to evaluate you can wrtite a small view that extracts the data you need...

By the way... Why is there no SQL Code button on the form ;)

create view DocCount
as
select (select count(*) from Documents where type = 'Invoice') NrOfInvoices,
(select count(*) from Documents where type = 'Mail') NrOfMails

or another way...

create view DocCount2
as
select type, count(*) from Documents group by type

This way there is no need to update this table