Click here to Skip to main content
15,942,080 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello to All
I want to do the following
SQL
declare @no as varchar(50)
declare @TName as varchar(50)='tblOrder';
declare @CName as varchar(50)='OrdId';
declare @qry as varchar(500)
declare @count as int

set @qry='select '+@count+' = count(*) from '+@TName;
print (@qry)
exec(@qry)


because it is giving error:

Msg 245, Level 16, State 1, Line 7 - Conversion failed when converting the varchar value 'select ' to data type int.<br />


thanks
Posted
Updated 17-Mar-11 2:29am
v3

Try this

SQL
declare @no as varchar(50)
declare @TName as varchar(50)='tblOrder';
declare @CName as varchar(50)='OrdId';
declare @qry as nvarchar(500) -- varchar(500)
declare @count as int

set @qry='select @count = count(*) from '+@TName;
print (@qry)
--exec(@qry)
exec sp_executesql @qry, N'@count int OUTPUT', @count OUTPUT
select @count
 
Share this answer
 
Comments
Sharma Richa 18-Mar-11 0:22am    
Thanks for your help
try this:

set @qry='select '+cast(@count as varchar(32))+' = count(*) from '+@TName;
 
Share this answer
 
Comments
ashu2188 17-Mar-11 8:32am    
Its like converting integer to varchar...i guess it will work but my doubt is that wouldn't it work if we remove the single quotes as the single quotes takes arguments as string and by removing it , argument itself will be integer...!!
Manfred Rudolf Bihy 17-Mar-11 8:51am    
String concatenation requires that both operands be of type string (or xxxchar). SQL complains if one of the operands is non textual. Since the addition operator is identical (+) you could say the same for addition. The only way to know which operation to perform is if both operators have the same type. So it will be addiont for numerical operators and concatenation for XXXchar operators.
Manfred Rudolf Bihy 17-Mar-11 8:52am    
Proposed as answer! 5+
Sharma Richa 17-Mar-11 8:55am    
by doing so there is no error but the value of @count is null
even my table contain records
remove the single quotes where you had written '@count'
it should work.
 
Share this answer
 
Comments
Sharma Richa 17-Mar-11 8:34am    
now the error is
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@count".
ashu2188 17-Mar-11 8:37am    
ok...try putting double quotes instead of single quotes.
Sharma Richa 17-Mar-11 8:41am    
double quotes .........................are u sure?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900