return value,

[Hema Bairavan]

Hi, @Text ='%hema%' @itemcount int SET @SQL ='SELECT COUNT(*) FROM dbo.table WHERE (LOWER(column) LIKE LOWER('+@Text+') OR LOWER(column) LIKE LOWER('+@Text+')) exec @SQL i want out put of that select (i.e count(*)) in the local variable @itemcount. if i give like set @itemcount = EXEC @SQL i m getting syntax error, how to take that?

[Blue_Boy]

Example:

declare @itemcount int
set @itemcount = (select isnull(count(*),0) from tablename where columnname=conditionvalue)

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Hema Bairavan]

thanks for your response blue. My question is executing the query dynamically that is using EXEC 'query' i need this output in a variable. i hope you can get me or read my prev message fully

[DarrenShultz]

You may want to take a look at the sp_executesql system stored procedure: http://msdn.microsoft.com/en-us/library/ms188001.aspx[^] This allows you execute the dynamic SQL string, as well as specify parameter definitions and parameter assignments that can bind local SQL variables to the dynamic SQL call.

DECLARE @InputParam int,
@OutputParam int

EXECUTE sp_executesql 'query',
N'@DynamicInputParam int, @DynamicOutputParam int OUTPUT',
@DynamicInputParam = @InputParam,
@DynamicOutputParam = @OutputParam OUTPUT

Within 'query', use the dynamic parameters/variables defined in the 2nd parameter (@DynamicInputParam and @DynamicOutputParam). Within 'query', these variables will initially contain the values of (@InputParam and @OutputParam). If a dynamic parameter is defined as an OUTPUT parameter (@DynamicOutputParam), any assignment to that variable will be reflected in the local variable (@OutputParam) after the call to sp_executesql.