| Author |
 Topic  |
|
|
casati74
Posting Yak Master
Italy
109 Posts |
 Posted - 09/06/2006 : 06:19:08
|
hello i wrote this query but when i lunch it don't run how????
set @sql ='SELECT DISTINCT '''+@HeatNumber+'''=Heats.HeatNumber,
HeatOrders.OrderNumber, Heats.[Date]
FROM HeatEditedChemistry INNER JOIN Replications ON HeatEditedChemistry.HeatEditedChemistryID = Replications.IdRegister INNER JOIN Heats ON HeatEditedChemistry.HeatID = Heats.HeatID LEFT OUTER JOIN HeatOrders ON Heats.HeatID = HeatOrders.HeatID WHERE (eVisionDonasid..Replications.IdRegister =1571)'
Where is the error???
Thank's |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/06/2006 : 06:20:13
|
What IS the error?
Maybe you need to convert @HeatNumber to a VARCHAR when concatenating strings?
set @sql ='SELECT DISTINCT Heats.HeatNumber AS '' + @HeatNumber + ''
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 09/06/2006 06:24:37 |
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/06/2006 : 06:23:24
|
Obviously it won't run because you are trying to assign value to a variable in Dynamic SQL. Why don't you do it directly. What is the purpose of using D-SQL?
SELECT DISTINCT @HeatNumber = Heats.HeatNumber,
HeatOrders.OrderNumber, Heats.[Date]
FROM HeatEditedChemistry INNER JOIN Replications ON HeatEditedChemistry.HeatEditedChemistryID = Replications.IdRegister INNER JOIN Heats ON HeatEditedChemistry.HeatID = Heats.HeatID LEFT OUTER JOIN HeatOrders ON Heats.HeatID = HeatOrders.HeatID WHERE (eVisionDonasid..Replications.IdRegister =1571)
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
casati74
Posting Yak Master
Italy
109 Posts |
Posted - 09/06/2006 : 06:28:03
|
When I exec (@sql) he don't return any value, but if i write
set @sql ='SELECT DISTINCT ''+@HeatNumber+''=Heats.HeatNumber
He return a select result witout assign value to a variable @HeatNumber.
es
exec @sql
1250 null 31/08/2006
print @HeatNumber
or select * from tabella where Heatnumber = @HeatNumber
He don't return anything
Why |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/06/2006 : 06:31:05
|
quote:
He return a select result witout assign value to a variable @HeatNumber.
Have a look in bookonline for sp_Executesql
Chirag |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/06/2006 : 06:35:06
|
DON'T USE DYNAMIC SQL FOR THIS!
And you can't mix selecting values into variables together with viewing data.
This is how your query look likeSELECT @HeatNumber = Heats.HeatNumber,
HeatOrders.OrderNumber,
Heats.[Date]
FROM HeatEditedChemistry
INNER JOIN Replications ON HeatEditedChemistry.HeatEditedChemistryID = Replications.IdRegister
INNER JOIN Heats ON HeatEditedChemistry.HeatID = Heats.HeatID
LEFT JOIN HeatOrders ON Heats.HeatID = HeatOrders.HeatID
WHERE eVisionDonasid..Replications.IdRegister = 1571 You return three values (columns) but only store one of them?
What is you objective? Haven't you learned yet to explain
1. What you want to be done
2. What you have tried this far
3. What is the source of the data (sample value and table definitions)
4. What is the expected output from the supplied sample data
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 09/06/2006 06:35:53 |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/06/2006 : 06:35:39
|
quote:
Originally posted by casati74
When I exec (@sql) he don't return any value, but if i write
set @sql ='SELECT DISTINCT ''+@HeatNumber+''=Heats.HeatNumber
He return a select result witout assign value to a variable @HeatNumber.
es
exec @sql
1250 null 31/08/2006
print @HeatNumber
or select * from tabella where Heatnumber = @HeatNumber
He don't return anything
Why
I don't know how you are able to run your EXEC statement without any error, if you are running the query you posted, since at the same time you are assigning value of one field to a variable and also, displaying some other fields (HeatOrders.OrderNumber, Heats.[Date]) which is simply not allowed?
Please explain your scenario clearly!!
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
casati74
Posting Yak Master
Italy
109 Posts |
Posted - 09/06/2006 : 08:37:18
|
i have this select
set @sql ='SELECT DISTINCT Heats.HeatNumber, HeatOrders.OrderNumber, Heats.[Date]FROM '+@InvolvedTable+' INNER JOIN Replications ON '+@InvolvedTable+'.HeatEditedChemistryID = Replications.IdRegister INNER JOIN Heats ON '+@InvolvedTable+'.HeatID = Heats.HeatID LEFT OUTER JOIN HeatOrders ON Heats.HeatID = HeatOrders.HeatID
WHERE (eVisionDonasid..Replications.IdRegister ='''+@IdRegister+''')'
and i want assign a result to a thre variable @HeatNumber,@OrderNumber, @date because I use this data in other select.
How can i do it?!?!?
thank's |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/06/2006 : 09:36:30
|
casati74 -- sounds like you have a really bad database design; table names should never be parameters! I hope this is something that you can change ... with a good design, things are much, much easier and there is no need for dynamic sql. Doesn't it seem like simple things to do are very complicated?
You should never have 1 table per employee or customer or something like that; you should have a table of *all* employees or *all* customers, with 1 row for each.
- Jeff |
Edited by - jsmith8858 on 09/06/2006 09:37:33 |
|
|
| |
Topic |
|
@sql
Forum Locked
