pass table name to sql store procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 17-Mar-11 2:20am

Sharma Richa

Updated 17-Mar-11 2:29am

#realJSOP

v3

Add a Solution

3 solutions

Solution 2

try this:

set @qry='select '+cast(@count as varchar(32))+' = count(*) from '+@TName;

Posted 17-Mar-11 2:29am

#realJSOP

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

Solution 1

remove the single quotes where you had written '@count'
it should work.

Posted 17-Mar-11 2:27am

ashu2188

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?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Costica U · Accepted Answer · 2011-03-17T03:27:00

Solution 3

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

Posted 17-Mar-11 3:27am

Costica U

Comments

Sharma Richa 18-Mar-11 0:22am

Thanks for your help