SProc - Ad hoc sql statement with COUNT for exec(@SQL) ??

[Judith Farber Abraham]

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

[Ananth]

If I understand correctly, you're trying to get a value into an @variable from a dynamic sql statement. This is not possible. The only variables you can use in a dynamic sql are those declared within the string itself...you cannot declare a variable outside, and then try to assign a value through a dynamic query.


------------
Judith Farber Abraham at 5/14/01 3:04:50 PM

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

[S]

------------
Ananth at 5/14/01 3:35:38 PM

If I understand correctly, you're trying to get a value into an @variable from a dynamic sql statement. This is not possible. The only variables you can use in a dynamic sql are those declared within the string itself...you cannot declare a variable outside, and then try to assign a value through a dynamic query.


------------
Judith Farber Abraham at 5/14/01 3:04:50 PM

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

[Sundar]

If you are trying to cater for a multiude of WHERE clauses but minimising the coding you may want to look at the CASE statement.


------------
Ananth at 5/14/01 3:35:38 PM

If I understand correctly, you're trying to get a value into an @variable from a dynamic sql statement. This is not possible. The only variables you can use in a dynamic sql are those declared within the string itself...you cannot declare a variable outside, and then try to assign a value through a dynamic query.


------------
Judith Farber Abraham at 5/14/01 3:04:50 PM

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

[Chris Thibodeaux]

Hey Judith -
This should work for you. FYI - I used the pubs db for this data...

DECLARE @Sql varchar(500), @Tbl varchar(20), @Where varchar(50)
SET @Tbl = 'Titles'
SET @Where = 'Type = ''Business'''
SET @sql = 'Declare @test int'+CHAR(13)+
'SELECT @Test = COUNT(*) FROM '+@tbl+' WHERE '+@Where+CHAR(13)+
'SELECT @Test'
EXEC(@sql)


------------
Judith Farber Abraham at 5/14/01 3:04:50 PM

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith