SQL Query Error - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Error

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-06-17 : 22:16:15

I had the following query:

DECLARE @FieldID nvarchar(500) ='Payment_ID'
DECLARE @strSNO varchar(50)='3319F8C4-23B5-E111-8738-02215E0AF813'
DECLARE @strSNO1 uniqueidentifier

SET @strSNO1 = cast(@strSNO as uniqueidentifier)

exec ('SELECT ' + @FieldID + ' from AES_LWM.TRANSACTIONS.RT_Receipt_Detail Where SNo = ' + @strSNO1 + '')

I get error Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'F8C4'.
when trying to execute the query...Can anyone help to check what is the problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-17 : 22:21:02

[code]
exec ('SELECT ' + @FieldID + ' from AES_LWM.TRANSACTIONS.RT_Receipt_Detail Where SNo = ''' + @strSNO1 + '''')
[/code]

have a look at http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-06-17 : 22:30:34

Thanks.. got it... But how can i execute this query and get the return value into a variable

DECLARE @FieldValue

How can I fill @FieldValue with the return value get from the query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-17 : 22:32:44

use sp_executesql and pass in an OUTPUT parameter. Take a look at the link that i posted

KH
[spoiler]Time is always against us[/spoiler]

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-06-17 : 22:43:57

I tried this :

DECLARE @sql nvarchar(4000)
DECLARE @params nvarchar(4000)
DECLARE @FieldID varchar(50) = 'Payment_ID'
DECLARE @count varchar(50)
-- SET @sql = 'SELECT ' + @FieldID + ' from AES_LWM.TRANSACTIONS.RT_Receipt_Detail Where SNo = ''' + @SNO1 + ''''

-- exec sp_executesql ('SELECT ' + @FieldID + ' from AES_LWM.TRANSACTIONS.RT_Receipt_Detail Where SNo = ''' + @SNO1 + '''')

SELECT @sql ='SELECT @cnt = ' + @FieldID + ' from AES_LWM.TRANSACTIONS.RT_Receipt_Detail Where SNo = @SNO '
SELECT @params = N'@FieldID varchar(50), ' +
N'@SNO uniqueidentifier,' +
N'@cnt varchar(50) OUTPUT'
EXEC sp_executesql @sql, @params, @FieldID, '3319F8C4-23B5-E111-8738-02215E0AF813', @cnt = @count OUTPUT

PRINT @sql

How can I fill @FieldValue with the return from this?

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-06-17 : 22:45:36

I had figure it out..Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources