append string to query - 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 2000 Forums

Transact-SQL (2000)

append string to query

Author

Topic

ram_01
Starting Member

1 Post

Posted - 2006-11-03 : 01:59:05

hello all,

i have a problem appending a variable declared as varchar
to a query

the below code works

declare @str varchar(100),@str2 varchar(1000),@orgid int
set @str = 'sum(a.CY_Sales_CropProtection) >100000 '
set @orgid=1
set @str2 =(select sum(a.CY_Sales_CropProtection) from usa_data a , tmpstep3 b where (a.orgid<>''+@orgid+'')
and (a.CY_RC_Chain=b.RetailCategory_chain)
having (sum(a.CY_Sales_CropProtection) >100000))
print @str2

but when i try to append i get an incorrect syntax error,

declare @str varchar(100),varchar(100),@str2 varchar(100),@orgid int
set @str = 'sum(a.CY_Sales_CropProtection) >100000 '
set @orgid=1
set @str2 =(select sum(a.CY_Sales_CropProtection) from usa_data a , tmpstep3 b
where (a.orgid<>''+@orgid+'')
and (CY_RC_Chain=b.RetailCategory_chain)
having (+@str))
print @str2

kindly let me know how to solve this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-03 : 02:46:09

declare @str varchar(100),varchar(100),@str2 varchar(100),@orgid int

set @str = 'sum(a.CY_Sales_CropProtection) >100000 '
set @orgid=1
set @str2 ='(select sum(a.CY_Sales_CropProtection) from usa_data a , tmpstep3 b where (a.orgid<>' + @orgid + ')
and (CY_RC_Chain=b.RetailCategory_chain)
having (' + @str + '))'
print @str2

Peter Larsson
Helsingborg, Sweden

EDIT: PS. If possible, stay away from dynamic SQL.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-03 : 02:50:53

The first code will not work as SQL code, is it from an application? It looks like perhaps what you're trying to do is something like this


declare @str varchar(100), @str2 varchar(1000), @orgid int
set @str = 'sum(a.CY_Sales_CropProtection) > 100000'
set @orgid=1
set @str2 = '(select sum(a.CY_Sales_CropProtection) from usa_data a , tmpstep3 b
where (a.orgid <> ' + cast(@orgid AS varchar(10)) + ') 
and (CY_RC_Chain=b.RetailCategory_chain)
having (' + @str + '))' 
print @str2

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-03 : 03:41:55

quote:
Originally posted by Peso

declare @str varchar(100),varchar(100),@str2 varchar(100),@orgid int

set @str = 'sum(a.CY_Sales_CropProtection) >100000 '
set @orgid=1
set @str2 ='(select sum(a.CY_Sales_CropProtection) from usa_data a , tmpstep3 b where (a.orgid<>' + @orgid + ')
and (CY_RC_Chain=b.RetailCategory_chain)
having (' + @str + '))'
print @str2

Peter, you missed a couple of errors in the original post - extra varchar in the declare that is probably what was causing rams syntax error, @str2 is too short to hold the result, and @orgid is an int, so needs to be cast to a varchar.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-04 : 06:32:22

As said, avoid Dynamic SQL as much as possible
or read this www.Sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources