question

cchapel avatar image
cchapel asked

How to create a scalar function with parameter in the select statement?

I am getting an error: Must declare the table variable "@TableName" when trying to create this function:

ALTER FUNCTION [dbo].[fnCountRecords](@TableName nvarchar(128),@ProjectID int)

RETURNS int

AS

BEGIN

RETURN (SELECT COUNT(1) FROM @TableName WHERE ProjectID=@ProjectID)

END

GO

Any help would be appreciated! Thanks!

parametersscalar-functions
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Also, with either of the approaches posted below (which are, in fact, the same approach, but @WRBI has more detail about what's going on), you're exposing yourself to SQL Injection attacks...

Consider what happens if the `@tablename` parameter is given a value of `'tablename; drop table tablename'` - you get the answer you expect, and, as a bonus, your table is dropped.

Obligatory reference to Little Bobby Tables, aka https://xkcd.com/327

1 Like 1 ·
WRBI avatar image WRBI ThomasRushton ♦♦ ·

I'm loving that little comic strip :D

1 Like 1 ·
cchapel avatar image cchapel ThomasRushton ♦♦ ·

Thomas, thanks for the reply. An extremely important comment regarding SQL Injection attacks. I need to come up with another approach. Any suggestions would be helpful. Thanks.

0 Likes 0 ·

As Thomas points out, performance is an issue.

What are you trying to achieve? I.e. Can a sproc be used and you use the OUTPUT clause? If this is in a SPROC already then why not do the dynamic SQL in there rather than calling for a function? Alternatively you get all the names from information schema that have a productid and get the counts for each table into a temptable and then query the temp table for what you need (or loop through them).

0 Likes 0 ·

WRBI,

I am trying to count records in a list of tables which are filtered by an id (ProjectID) in order to determine if certain steps have been completed (records inserted into specific tables), similar to how LinkedIn determines your profile strength. The list of tables is included in a separate table.

The attached image sql-view.jpg is close to the solution, however, I am not able to filter by ProjectID.

Perhaps there is even a simpler solution.

Thanks.

0 Likes 0 ·
sql-view.jpg (218.7 KiB)

I've deleted my original answer. I wasn't happy with potential performance and injection implications.

@cchapel - I think my personal preference here'd be to have another table with an FK to ProjectID. You could then either store a % or volume in the table to let you know where the strength of the Project. It could either be done in a SPROC or in a trigger(?) It could also be easily decreased if things are rolled backwards.

Alternatively if its a DW then you can use a Periodic Snapshot Fact Table. Then track the dates that the entries are made to each of the tables. (I think this is only an option if your not adding in loads of tables in the future otherwise you'll have to keep adding columns).

0 Likes 0 ·

Thanks WRBI,

I will consider your suggestion for tracking the strength of the Project profile in a related table!

0 Likes 0 ·

1 Answer

· Write an Answer
Dataman avatar image
Dataman answered

Hey @cchapel!

You are attempting to use static SQL with a dynamic query. In order to use a dynamic table name, you must use dynamic SQL.

Here is what I recommend you try

ALTER FUNCTION [dbo].[fnCountRecords](@TableName nvarchar(128),@ProjectID int)
RETURNS int
AS
BEGIN
DECLARE @Sql NVARCHAR(150) = 'SELECT COUNT(*) FROM ' + @TableName

RETURN EXEC(@Sql)
END
GO

Let me know if this works for you!

Best wishes,

Dataman.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dataman, thanks for the reply! I am getting an error: RETURN statements in scalar valued functions must include an argument. Also, it seems that I am not able to include dynamic SQL inside of a function.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.