Evaluate a string expression in sql

Question

5.00/5 (1 vote)

See more:

I have an expression stored in string such as :

@myString='1 * (1 + 0)'

I want to evaluate this,how can i do it?

Using

SQL

EXEC('SELECT 1*(1+0)')

it is possible ,but now the expression is in string variable @myString then what should be the steps??

Posted 11-Aug-14 20:53pm

NiteshPanj

Add a Solution

3 solutions

Solution 2

Hi,

Check below...

SQL

--PR_TEST_OP '1 * (1 + 0)'
CREATE PROC PR_TEST_OP
(
@myString as varchar(100)
)
AS 
BEGIN

declare @@SQL VARCHAR(200)
DECLARE @CNT INT 

--SET @myString='1 * (1 + 0)'

SET @@SQL=' SELECT ' + @myString
PRINT (@@SQL)

CREATE TABLE #TMP_1
(col1 int)

INSERT INTO #TMP_1
EXEC(@@SQL)

SELECT @CNT=col1 FROM #TMP_1


IF @CNT >0 
BEGIN

PRINT('OK')

END
ELSE
BEGIN

PRINT('NOT OK')

END


END

Hope this will help you.

Cheers

Posted 11-Aug-14 21:51pm

Magic Wonder

Comments

NiteshPanj 12-Aug-14 4:07am

exactly wat i wanted thnx

Magic Wonder 12-Aug-14 4:59am

Welcome a..Gain.

NiteshPanj 12-Aug-14 8:39am

final que can u help me in assigning exec(@@sql) value to sql variable as i dant want to create a table

Magic Wonder 12-Aug-14 9:20am

Check Solution 3.

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)

Magic Wonder · Accepted Answer · 2014-08-11T20:58:00

Solution 1

Hi,

Check This

SQL

declare @@SQL VARCHAR(200)

DECLARE @myString VARCHAR(200)

SET @myString='1 * (1 + 0)'

SET @@SQL='SELECT ' + @myString

EXEC(@@SQL)

Hope this will help you.

Cheers

Posted 11-Aug-14 20:58pm

Magic Wonder

Comments

NiteshPanj 12-Aug-14 3:04am

How to display result of EXEC(@@SQL)??It is not showing

Magic Wonder 12-Aug-14 3:08am

If you want to display the query....use Print(@@SQL)

NiteshPanj 12-Aug-14 3:06am

done thanxx

Magic Wonder 12-Aug-14 3:08am

Your Welcome

NiteshPanj 12-Aug-14 3:30am

I want to use if condition on result obtained of exec(@@sql) like
if(Exec(@@sql)>0)
then
so....
else
so.....

can u help in this??

thanxx

Magic Wonder 12-Aug-14 3:51am

Check new Solution

Magic Wonder · Accepted Answer · 2014-08-12T03:20:00

Hi,

Try this one....

SQL

--PR_TEST_OP_NEW '1 * (1 + 0)'
CREATE PROC PR_TEST_OP_NEW
(
@myString as varchar(100)
)
AS 
BEGIN
declare @mSQL NVARCHAR(200)
DECLARE @CNT AS INT

SET @mSQL='
SELECT @CNT= ' +  @myString

Print(@mSQL)

EXEC sp_executesql
  @mSQL,N'@CNT INT OUTPUT',@CNT OUTPUT

SELECT @CNT

if @CNT > 0 
BEGIN

Print('Done')

END
ELSE
BEGIN

Print('Not Done')
END 

END

Hope this will help you.

Also, go through this sp_executesql[^]

Cheers