November 3, 2004 at 11:01 am
How can I get the variable name to insert it's literal value into the query so can get something like this to work:
Declare @table
Set @table = 'mytable'
Select * from @table
I'm trying to cleanse my source data by determining which columns can be eliminated in my ETL process. I've created a stored procedure to get the count of non-empty or non-zero amounts per column depending on the column type, but I'm having a problem parameterizing the from statement and where clauses. The procedure won't complie, I get the message:
Server: Msg 137, Level 15, State 2, Procedure FindEmpty, Line 28
Must declare the variable '@table'.
Server: Msg 137, Level 15, State 1, Procedure FindEmpty, Line 41
Must declare the variable '@table'.
This is because it ovbiously hates seeing a variable in the from clause. It also doesn't like the column names parameterized in the from clause.
Here is my full procedure:
ALTER PROCEDURE FindEmpty
@table varchar(100),
@threshold int
AS
Declare @count int, @column varchar(250), @type int
Create table #output ([Column Name] varchar(250), [Non Empty Count] int)
Declare cols Cursor FAST_FORWARD FOR
Select c.name, t.name
From syscolumns c
inner join sysobjects o on o.id = c.id
inner join systypes t on t.xtype = c.xtype
where o.name like @table order by colorder
Open cols
Fetch next from cols into @column, @type
While (@@Fetch_Status = 0)
Begin
--For Numeric Types
If @type in ('decimal','int','number','money')
Begin
Select @count = count(*)
from @table
where @column <> 0
If @count > @threshold
Begin
Insert Into #output ([Column Name], [Non Empty Count])
Values (@column,@count)
End
End
--For characterTypes
If @type in ('nvarchar','char','varchar','nchar')
Begin
Select @count = count(*)
from @table
where RTRIM(@column) <> ''
If @count > @threshold
Begin
Insert Into #output ([Column Name], [Non Empty Count])
Values (@column,@count)
End
End
Fetch next from cols into @column
End
Select * from #output
Thanks, sorry if this post is hard to read.
November 3, 2004 at 11:29 am
Edited post...
With a query the size of the one you are working with, this will get really ugly, but, you can do this with dynamic SQL (which is generally frowned upon)...
DECLARE
@table sysname,
@command varchar(1000)
SET @table = 'mytable'
SET @command = 'SELECT * FROM' + @table
EXEC (@command)
--Steve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply