Assigning query value to a variavle

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Assigning query value to a variavle

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-08-17 : 09:39:28

Hi,

I want to assign a query to a variable

SELECT @SQL =IF EXISTS(SELECT 1 FROM cam_claims WHERE LTRIM(SUBSTRING(claim_number,0,7)) <> @ClientCode
AND [file_id] = @fileName))
EXEC @SQL
IF (@@ERROR <> 0)
BEGIN
SELECT @i_ReturnCD = 8
SELECT @v_ErrorMsg = 'There are some incorrect records that got populated. Please correct them and resubmit'
GOTO ERRORHANDLER
RETURN
END

But its throwing error incorrect syntax near IF and select statement.even if i put single quotes inside it it throws error.Can anyone help me on this

susan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-17 : 10:00:42

Put () around the statement:

EXEC (@sql)

Jim

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-08-17 : 10:17:01

It is throwing error to declare the variable client code which i have passed as parameter to the SP

susan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-17 : 10:26:46

try like this
decalre @ClientCode varchar(32),@SQL varchar(max),@fileName varchar(max)

SELECT @SQL ='SELECT 1 FROM cam_claims WHERE LTRIM(SUBSTRING(claim_number,0,7)) <> '+@ClientCode+'
AND [file_id] = '+@fileName+'

print @sql

EXEC (@SQL)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-17 : 10:39:39

Why use dynamic sql at all?

You are comparing a static table and static columns against variables.

No need for any dynamic sql.

T don't think what you have written is going to do what you expect.

What are you actually trying to do? Check that a particular entry already exists?

Take a step back and tell us what you are *trying* to do and what your end goal is.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

MonikaK
Starting Member

6 Posts

Posted - 2009-08-17 : 10:42:52

Hi Susan,
Is it this query :
(SELECT 1 FROM cam_claims WHERE LTRIM(SUBSTRING(claim_number,0,7)) <> @ClientCode
AND [file_id] = @fileName)
that you want to assign to the variable @SQL?

Please explain the purpose of this query and the structure of the table beneath for better understanding.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 10:52:55

[code]IF EXISTS(SELECT * FROM cam_claims WHERE LTRIM(SUBSTRING(claim_number, 1, 7)) <> @ClientCode AND [file_id] = @fileName)
BEGIN
SELECT @i_ReturnCD = 8,
@v_ErrorMsg = 'There are some incorrect records that got populated. Please correct them and resubmit'

GOTO ERRORHANDLER
END[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources