daynamic select query execute problem

Question

0.00/5 (No votes)

See more:

here is my stored procedure .. i give column name dynamically

SQL

@datafield varchar(30) --> this column name which i enter
AS
BEGIN
SELECT cust_id  FROM custm_master where @datafield = 1
END

exec cust_search 'cust_id' --> column name

i got an error :

SQL

Conversion failed when converting the nvarchar value '[cust_id]' to data type int.

Posted 6-Oct-13 5:05am

priyanshbhaliya

Add a Solution

2 solutions

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)

OriginalGriff · Accepted Answer · 2013-10-06T05:15:00

Solution 1

You can't do it like that: The parameter substitution is done at a different stage of the SQL processing, so that won't work. What you have to do is create a string and execute that:

SQL

DECLARE @CMD VARCHAR(MAX)
SET @CMD = 'SELECT cust_id FROM custm_master WHERE ' + @datafield + '=1'
EXEC(@CMD)

Posted 6-Oct-13 5:15am

OriginalGriff

Comments

priyanshbhaliya 6-Oct-13 11:29am

thnks and can u explain why my query give error to convert column name to int

OriginalGriff 6-Oct-13 11:39am

It has to do with the order in which things happen.
The query is processed into an internal form and is systemax checked at the point.
When this parser looks at the query is assumes that the condition is trying to compare a parameter value with a number - so it generates internal code to convert your parameter value to an integer when it gets to the execution stage.
When that code gets executed, it finds it's not convertible and throws an error.

priyanshbhaliya 6-Oct-13 11:52am

thnks

priyanshbhaliya 6-Oct-13 12:08pm

ur query give an error : must declare scalar variable '@datafield'

OriginalGriff 6-Oct-13 12:12pm

It's your parameter name - so you need that code in your SP...

Madhu Nair · Accepted Answer · 2013-10-06T05:17:00

Solution 2

try this way-

SQL

@datafield varchar(30) --> this column name which i enter
AS
BEGIN
declare @SQL varchar(8000)
declare @val varchar(100)='1'
set @SQL='SELECT cust_id  FROM custm_master where  '+ @datafield +' = ''' + @val+''''
exec (@sql)
END

Posted 6-Oct-13 5:17am

Madhu Nair

Comments

priyanshbhaliya 6-Oct-13 11:28am

thnks and can u explain why my query give error to convert column name to int

priyanshbhaliya 6-Oct-13 11:59am

why u give @val field 3 quote in both side ???

Madhu Nair 7-Oct-13 1:43am

you can remove it if not required.... i have used it in case you want to pass varchar data

priyanshbhaliya 8-Oct-13 3:43am

ok thnks