Paasing TableName as parameter to SP

The procedure is something like below will not execute because we can not pass table name as parameter and use in select statement inside SP.

CREATEPROCEDURE myProc @table_name varchar(1024)AS
BEGIN
DECLARE@Nval INT 
    SELECT @Nval  = COUNT(*)FROM@table_name    -- Error
END

Solution:

ALTERPROCEDURE[dbo].[sp_tablenametest]
@table_name varchar(50),
@PMId int,@ValueEq int

AS
BEGIN
SET NOCOUNT ON;
DECLARE@cmd AS NVARCHAR(max)

SET@cmd = N'SELECT * FROM '+@table_name +' WHERE Column1 = '''+@PMId +''''+' AND Column2= '''+@ValueEq +''''

EXEC sp_executesql @cmd 
END
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s