Problem in writing a dynamic sql query

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 2000 Forums

Transact-SQL (2000)

Problem in writing a dynamic sql query

Author

Topic

tiwari_neha
Starting Member

4 Posts

Posted - 2009-01-12 : 01:30:55

Hi,

I am facing an issue in writing a sql query.

DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
exec (@query)

In this query, I am converting a hexadecimal value to Int. My problem is in a single select statement I want few mpre fields (eg:ww8_mtr_no, Int datatype) to be extracted.

I tried writing it like this:

DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT ww8_mtr_no,CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
exec (@query)

Error: Invalid column name 'ww8_mtr_no'.

Then I tried:

DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT' + ww8_mtr_no+',CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
exec (@query)

error: Syntax error converting the varchar value ' SELECT ' to a column of data type int.

Can anyone plz suggest how to write it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 01:36:26

There is not point to using dynamic SQL here. Could you explain what part of your query is supposed to be dynamic as I am not seeing it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tiwari_neha
Starting Member

4 Posts

Posted - 2009-01-12 : 03:02:40

You are right...No part is dynamic.

I just want to get few int data type fields in the same select query, so that when I generate xmldocument from the dataset, it will not come under diferent root tag.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 03:16:00

then why use EXEC()?cant you directly execute select?

also does ww8_mtr_no exist in any of tableS?

tiwari_neha
Starting Member

4 Posts

Posted - 2009-01-12 : 03:23:44

It cant be executed directl;y becoz its hex to int conversion, see below solutions you will understand the problem:

Soln 1: select convert(int,'0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6)) from dpsettings

O/p: Syntax error converting the varchar value '0X107AC0' to a column of data type int.

Sloution 2: DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT ww8_mtr_no,CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
print (@query)

O/p: SELECT ww8_mtr_no,CONVERT(INT, 0X107AC0) / 100.0 as AccountBalance

Since in the Query I have not mentioned the table name, "Invalid column name 'ww8_mtr_no'." is coming.

Solution3: DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT' + ww8_mtr_no+',CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
print (@query)

O/p: Not getting output, "Syntax error converting the varchar value ' SELECT ' to a column of data type int", becoz @query is string and ww8_mtr_no is integer.

Solution 4:

DECLARE @QUERY varchar(8000),@s as varchar

SELECT @Query =

' SELECT ' + 'ww8_mtr_no'+',CONVERT(INT, ' + '0X' + 'SUBSTRING(DPS_POSTAGE_ID, 21 ,6)' + ') / 100.0 as AccountBalance '

+ 'FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530'

print (@query)

O/p:

SELECT ww8_mtr_no,CONVERT(INT, 0XSUBSTRING(DPS_POSTAGE_ID, 21 ,6)) / 100.0 as AccountBalance FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530

See error in 0X concatenation, this concatenation is required to convert hex into int.while executing it will say "Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'SUBSTRING'."

Please suggest what can be done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:32:11

What datatype is column DPS_POSTAGE_ID?

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

tiwari_neha
Starting Member

4 Posts

Posted - 2009-01-12 : 03:37:49

DPS_POSTAGE_ID is of varchar(50) datatype

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-12 : 04:00:00

Wat i think is its not possible to convert Varchar datatype into Int datatype.......

well looking forward for peso Sir reply......
Thanks,,,
May be i also get a new thing to learn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources