CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Middleman
Middleman

Reputation: 111

SQL Server | Is my stored procedure is OK?

CREATE PROCEDURE spCountTableRowWHere
    @TblName VARCHAR(50),
    @TblID VARCHAR(10) = 'Id',
    @WhereClause NVARCHAR(500) = '1=1'
AS
BEGIN
    DECLARE @Query NVARCHAR(500)
    DECLARE @ParamDefinition NVARCHAR(40)
    DECLARE @Count INT
    SET @Query = 'SELECT @C = COUNT('+@TblID+') FROM '+@TblName+' WHERE '+@WhereClause
    SET @ParamDefinition = '@C INT OUTPUT'

    EXECUTE SP_EXECUTESQL @Query, @ParamDefinition, @C = @Count OUTPUT
    SELECT @Count
END

I am wondering if this kind of procedure is better than a separate procedures for different tables.

Upvotes: 2

Views: 63

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

The short answer is no.
The long answer is that it is an opening to sql injection attacks.
Think what will happen if someone will put the following string in your where clause argument: 1=1; drop table myTable.

Upvotes: 4

Related Questions