SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 @sql
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/06/2006 :  06:19:08  Show Profile
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
30281 Posts

Posted - 09/06/2006 :  06:20:13  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/06/2006 :  06:23:24  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/06/2006 :  06:28:03  Show Profile
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
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/06/2006 :  06:31:05  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
quote:
He return a select result witout assign value to a variable @HeatNumber.



Have a look in bookonline for sp_Executesql


Chirag
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/06/2006 :  06:35:06  Show Profile  Visit SwePeso's Homepage
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 like
SELECT		@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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/06/2006 :  06:35:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/06/2006 :  08:37:18  Show Profile
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/06/2006 :  08:43:20  Show Profile  Visit SwePeso's Homepage
http://www.sommarskog.se/dynamic_sql.html


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/06/2006 :  09:23:59  Show Profile  Send madhivanan a Yahoo! Message
Why do you want to pass table name as parameter?
Also refer
http://www.nigelrivett.net/SQLTsql/sp_executesql.html


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/06/2006 :  09:36:30  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000