Quick beginner question about select in TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Quick beginner question about select in TSQL

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-13 : 16:06:10

Hi- Why can't you use a variable in a stored procedure the select statement... in this example:

CREATE PROCEDURE test_table(
@table varchar(50)
)
AS
declare @var varchar(100);

select @var = count(*) from @table

GO

Is there a better construction to use to accomplish that? Thanks so much

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-13 : 17:26:37

Yes. Use dynalic SQL Statement to achieve this.

CREATE PROCEDURE test_table(
@table varchar(50)
)
AS
declare @var varchar(100);
declare @sSQL varchar(100)

set @sSQL = 'select ' + @var + ' = count(*) from ' + @table

EXEC sp_executesql @sSQL

GO

With Regards
Sreenivas Reddy B

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 09:05:59

Thanks- So there is no way to actually get that value into a variable though within the stored procedure that can then be used through the stored procedure. Ultimately I'd like to do more than just print out the value, but actually use it for my counter.

Thanks so much for your input.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 09:07:27

I also get this error:

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 10
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-16 : 09:39:38

Is this you want?

DECLARE @x varchar(9), @sql1 nvarchar(200)
DECLARE @table varchar(20)
set @table='tt'
SELECT @sql1=N'select @x=count(*) from '+@table
EXEC sp_executesql @sql1, N'@x varchar(9) OUTPUT', @x output
Select @x

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources