Dynamic Statement help please

[grayt26]

Hi all,

I am struggling with a dynamic statement.

I have a insert into table command using two variables which have been declared eariler and a select statement. This all works find however once I put this into a dynamic statement it wants me to declare the variable again and lose everything that I have just fetched into that variable. Is there anyway around this so when I call my variables it will use the ones that I have already called.

 

[bborissov]

Can you post your Dynamic statement?
A shot in the dark:
You added these variables in the statement w/o concatenating the string. If so try this:

DECLARE @Test  int
DECLARE @Test1  varchar(50)

SET @Test  = 10
SET @Test1 = 'bla bla'

DECLARE @dynSQL varchar(200)
CREATE TABLE #Test (Fld1 int, Fld2 varchar(200))

SET @dynSQL = 'INSERT INTO #Test VALUES ('+CAST(@Test as varchar(20))+', '''+@Test1+''')'
EXEC (@dynSQL)
SELECT * from #Test
DROP TABLE #Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[grayt26]

This is my code, keeps coming up with an error no matter how i change it around so maybe it is not possible what i am trying to do here?


set @dynamicstate='INSERT INTO tests
([field_name],
[data_type],
[RESULT])
SELECT
' + @Column_name1 + ',
' + @Data_type1 + ',
SUM(' + @Column_name1 + ')
FROM testtable'

exec (@dynamicstate)

 

[bborissov]

What type are @Column_name1 and @Data_type1?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[grayt26]

both varchar 50

 

[bborissov]

What error you get?
I suppose you store a column names in these two variables, right?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[grayt26]

yeah

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'SE'.


SE is not in the code but is part of a field name?????

 

[SQLSister]

print your dynamic statment rather than executing it and you will probably see how it is building incorrectly

"NOTHING is more important in a database than integrity." ESquared

 

[grayt26]

Thanks for that, certainly has helped.


Think I have found my problem, looks like there SQL only lets you have a certain amount of characters in a dynamic statement. When I did a print I found that it was cutting my statement in half. Not sure if there is a way of increasing this limit.

 

[philhege]

what's the length of @dynamicstate? Can you control it?

 

[grayt26]

yep, lol my thought i did not have the variable length set with enough Characters. Thanks all, it is returning what i need.

Cheers