| Author |
Topic |
|
plan9
Starting Member
15 Posts |
 Posted - 2006-08-30 : 14:21:05
|
| HelloI'm trying to do a select and I'm having a problem with it (code below) declare @teste_varchar2 as varchar(20) declare @teste_varchar as varchar(500) set @teste_varchar2 = "valor_fact" exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' + @cont_descCursor) What is odd with the above code is that if I use a similar code but not dynamic sql it works.select valor_fact from ##CONTENC where contracto = @cont_descCursor |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-30 : 14:24:11
|
| What isn't working? Are you getting an error?Why do you even need dynamic SQL for this? Nothing in your posts suggests that you need it.Tara Kizer |
 |
|
|
plan9
Starting Member
15 Posts |
Posted - 2006-08-30 : 14:28:01
|
| the error is Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'e'.the reason why I need this is that I have to build a report from a temp table that a part of the columns are dynamically inserted because they vary dependingly of previous selected options by the user. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-30 : 14:33:16
|
| "valor_fact" --> 'valor_fact'Srinika |
|
|
|
plan9
Starting Member
15 Posts |
Posted - 2006-08-30 : 14:46:41
|
| It doesn't matter if I use " or 'in the debug phase before posting I've tried both |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-30 : 14:55:48
|
quote:
Originally posted by plan9
It doesn't matter if I use " or 'in the debug phase before posting I've tried both
R u using Microsoft SQL Server ? If so what version?What do u have in @cont_descCursor ?Srinika |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-30 : 15:04:47
|
| plan9, could you post your actual code as the code that you have posted works fine?Tara Kizer |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-08-30 : 15:52:21
|
quote:
Originally posted by plan9
the error is Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'e'.the reason why I need this is that I have to build a report from a temp table that a part of the columns are dynamically inserted because they vary dependingly of previous selected options by the user.
Your Temp table is out of scope; the context of exec() is different from the process that calls the Exec(). Sounds like you either have an unnormalized design (i.e., some columns show data for one year or month or customer, other columns for others) or you are mixing up presentation code with your data. Columns themselves (and table names) should not be dynamic -- only the rows and data that are returned should be. Even if you only want to display certain columns under certain conditions at the client or on a report (i.e., a user can hide columns on a grid to see only what they want) your SQL statements should always return and work with a consistent set of columns with consistent names and datatypes.If you want more help, you should provide more specific details of what you are trying to do. Often when something is hard to do, it is becuase you are going about it the wrong way (or you have a bad database design).- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-30 : 18:17:51
|
| Jeff, isn't that the point of using global temporary tables? Shouldn't the ## variety be available in all scopes? I've got several DBA type stored procedures that use dynamic SQL with global temporary tables. Maybe I'm not understanding what the issue is.Tara Kizer |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-30 : 18:27:24
|
[code]exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ''' + @cont_descCursor + '''')[/code]
KH |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-08-30 : 18:33:41
|
quote:
Originally posted by tkizer
Jeff, isn't that the point of using global temporary tables? Shouldn't the ## variety be available in all scopes? I've got several DBA type stored procedures that use dynamic SQL with global temporary tables. Maybe I'm not understanding what the issue is.Tara Kizer
Ah! you are correct, I didn't notice the double # ...That actually makes it even worse, due to potential concurrency issues, if multiple users are using the system.The rest of what I wrote certainly still applies.- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-30 : 18:47:57
|
| I agree.I only use ## for DBA type stuff.Tara Kizer |
|
|
|
plan9
Starting Member
15 Posts |
Posted - 2006-08-30 : 19:06:28
|
quote:
Originally posted by khtan
exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ''' + @cont_descCursor + '''')
KH
Yes I've come up with a similar solution the problem is that I want to attribute this exec to a variable how can I do it? |
|
|
|
plan9
Starting Member
15 Posts |
Posted - 2006-08-30 : 19:15:23
|
| in this case concurrency doesn't matter, it's a report as I said before, the number of periods or columns of this report depends on the a few options that the users choose or not it influences directly the number of columns, thats the reason of why the columns are dynamic, it makes the same sence has if I was using DBA procedures. I have a table that has same date periods and for each date period a percentage. the periods are user inputed.Each period are organized as interests family with a user entered name.What is done is to calculate the interests for some invoices in the periods of time entered by the user.So I have no way to statically create a report table with the calculations because I have no way to predict wich family will be choose, and because each number of periods in each family vary I need to recur to dynamic sql. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-30 : 19:15:33
|
use sp_executesql. It allows passing of parameters
KH |
|
|
|
|
problem with dynamic select