@sql - 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

Old Forums

CLOSED - General SQL Server

@sql

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-06 : 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

30421 Posts

Posted - 2006-09-06 : 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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-06 : 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

109 Posts

Posted - 2006-09-06 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 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

30421 Posts

Posted - 2006-09-06 : 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 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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-06 : 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

109 Posts

Posted - 2006-09-06 : 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

30421 Posts

Posted - 2006-09-06 : 08:43:20

http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 09:23:59

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-06 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources