How can I Pass a Table variable in Stored Procedure?

Question

Post reply

How can I Pass a Table variable in Stored Procedure?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Aparna-1 SSCrazy Points: 2339 More actions · Answer 1

You can pass table name. You can use dynamic sql in the stored procedure.

CREATE PROCEDURE test

@table_name varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

DECLARE @Sql VARCHAR(MAX)

SET @Sql = 'SELECT COUNT(*) FROM ' + @table_name

EXEC(@Sql)

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post new questions in a new thread. Thanks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

glennard.simpelo Grasshopper Points: 22 More actions · Answer 3

nice one that really worked! thank you so much!!

i have another question though, how about having two input parameters with the same stored procedure? i edited the code you did into this:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:<Author,,Name>

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

alter PROCEDURE sp_crows

@table1 varchar (50),

@table2 varchar (50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

declare @SQL1 varchar(max), @SQL2 varchar(max)

set @SQL1 = 'select count(*) from ' +@table1

set @SQL2 = 'select count(*) from ' +@table2

exec(@SQL1+@SQL2)

END

Go

the code compiled successfully meaning there was no error right? but once i execute a query using this stored procedure, i get this error message: "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '*'."

What could be wrong? Thanks a lot. Sorry for the noob questions.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Before you wander down this path, please be aware that this is seriously bad practice. It's a major SQL injection vulnerability, it's defeating the point of stored procedures.

If you want to know the row count in a table, providing it doesn't have to be 100% completely accurate at any point in time, you can query the system views.

DECLARE @TableName varchar(50);

SET @TableName = 'SomeTable';

SELECT object_name(object_id), SUM(rows) AS RowCount

FROM sys.partitions

WHERE object_id = OBJECT_ID(@TableName)

AND index_id IN (0,1);

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Aparna-1 SSCrazy Points: 2339 More actions · Answer 5

Thanks for reminding me the issues with dynamic SQL.

I have learnt a new solution to use system views for rowcounts.

Thanks again..

joshgeake SSC Enthusiast Points: 132 More actions · Answer 6

Yeah I wrote a post about this a while ago - essentially you need to form a dynamic sql statement by stitching together a string and then execute it.

See here for the full post - http://www.geakeit.co.uk/2011/02/05/a-table-input-variable-in-a-stored-procedure-and-bypassing-must-declare-the-table-variable-msg-1087/[/url]