Try this:

--------------------------------
declare @tablename char(40), @columnname char(40), @SEARCHSTRING
char(40), @sql nchar(255), @count int, @param nchar(40)

select @tablename = 'customers', @columnname = 'ContactTitle',
@SEARCHSTRING = 'Representative'

select @param = '@Count int output'

SET @SQL = 'SELECT count(*) FROM ' + Rtrim(@TABLENAME) + ' WHERE ' +
Rtrim(@COLUMNNAME) +' LIKE '+
+ ' ''%' + Rtrim(@SEARCHSTRING) + '%'' '

--First Method

EXEC SP_EXECUTESQL @sql, @Param, @Count OUTPUT

-- Second Method
exec (@sql)
------------------------------

First method is the recommended method.

John
www.ideo.com


-----Original Message-----
From: Usman Farhat [mailto:Usman.[Email Address Removed]
Sent: February 19, 2003 6:32 AM
To: LazyDBA.com Discussion
Subject: R: Dynamic Query Result


No this is not working ,still there is something that i missing.Can you
please help me out,

execute sp_executesql
N'select @Count = Count(*) from ALLCOLUMNSTOUPDATE ',
N'@Count Tinyint Output',
@Count Output

-----Messaggio originale-----
Da: Selva Balaji [mailto:[Email Address Removed] Wednesday, February 19, 2003 2:54 PM
A: Usman Farhat; LazyDBA.com Discussion
Oggetto: RE: Dynamic Query Result


Hi Usman,

you can do this with sp_executesql

SET @nvSqlSt = ' SELECT @Count = ISNULL(COUNT(*),0) FROM '+ @Table + '
WHERE '+ @Condition SET @nvParamList = '@Count int output' EXEC
SP_EXECUTESQL @nvSqlSt, @nvParamList, @Count OUTPUT

Regards,
Selva Balaji. B
"You must be the change, you wish to see in the world."

-----Original Message-----
From: Usman Farhat [mailto:Usman.[Email Address Removed] Wednesday, February 19, 2003 7:22 PM
To: LazyDBA.com Discussion
Subject: Dynamic Query Result


Hi everyone,

I have a dynamic sql string in which i want to calculate rows returned
by the query by assigning it to a variable
but the string followed will obviously will not work as between a
dynamic sql string we cant use variable enclosed in ' ' marks , what can
be possible solution for this sort of situation ..

SET @SQL = 'SELECT @Count = Count(*) FROM ' + @TABLENAME + ' WHERE ' +
@COLUMNNAME +' LIKE '+
+ ' ''' + @SEARCHSTRING + '%'' '
exec(@SQL)

Is there any way to return the querey result to variable using some
thing like this ? exec @tmp = (@SQL)

For example following query returns some rows count and i want to take
that value in some variable ,for
further processing.

SET @SQL = 'SELECT Count(*) FROM ' + @TABLENAME + ' WHERE ' +
@COLUMNNAME +' LIKE '+
+ ' ''' + @SEARCHSTRING + '%'' '

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page