Monday, March 12, 2012

Passing a dynamic table name to a stored procedure

Is this possible?

I know this doesn't work but I'll post it anyways so you can see what I'm trying to accomplish.

CREATE PROCEDURE GetStuff
@.something int,
@.tablename varchar(50)
AS
SELECT * FROM @.tablename WHERE something = @.something

Like I say, I know it doesn't work...but does anyone know how to accomplish something like this?there are some limitations to this code but it works for most parts :
using NOrthwind database :


CREATE PROC sp_distinctcount
@.table_name AS SYSNAME,
@.cat AS int

AS
DECLARE @.sql AS nvarchar(1000)
SET @.sql = N'SELECT *'+ N' FROM ['
+ @.table_name + N'] where categoryid=' + convert(varchar(10),@.cat)

EXEC sp_executesql @.stmt = @.sql

GO

from QA :


EXEC northwind..sp_distinctcount
@.table_name='categories',@.cat=1

hth

No comments:

Post a Comment