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.
Author |
Topic |
casati74
Posting Yak Master
109 Posts |
Posted - 2006-09-05 : 09:23:30
|
hello, I have written this stored proceduresdeclare @sql nvarchar(4000), @InvolvedTable nvarchar (100), @HeatNumber char(10), @IdRegister nvarchar(50),@sql1 nvarchar(4000), @FieldValue nvarchar (50)set @InvolvedTable = 'Heats'set @HeatNumber ='761501'set @IdRegister = '1329'set @sql = ''set @FieldValue = 'Name'execute qrySQl @InvolvedTable, @HeatNumber, @IdRegister, @sql, @FieldValueprint (@sql) The qrySQl would have to return the value to me of the variable one @sql but it is not therefore because? create procedure qrySql( @InvolvedTable nvarchar (100), @HeatNumber char(10), @IdRegister nvarchar(50), @sql nvarchar(4000)output, @FieldValue nvarchar (50) output)asdeclare @ConditionInsert bit, @ConditionField nvarchar (50), @FieldDate nvarchar (50), @FieldNumber nvarchar (50), @LocalField bit, @tagJointTable nvarchar (50), @tagLocalJointField nvarchar (50), @tagRemoteJointField nvarchar (50), @ConditionValue nvarchar (50), @JointTable nvarchar(50)SELECT @ConditionInsert = ScDon10..RepTagConfig.ConditionInsert, @ConditionField = ScDon10..RepTagConfig.ConditionField, @FieldDate = ScDon10..RepTagConfig.ConditionValue, @FieldNumber = ScDon10..RepTagConfig.FieldValue, @FieldDate = ScDon10..RepTagConfig.FieldDate, @FieldNumber = ScDon10..RepTagConfig.FieldNumber, @LocalField = ScDon10..RepTagConfig.LocalField, @tagJointTable = ScDon10..RepTagConfig.JointTable, @tagLocalJointField = ScDon10..RepTagConfig.LocalJointField, @tagRemoteJointField = ScDon10..RepTagConfig.RemoteJointFieldFROM ScDon10..RepTableConfig INNER JOIN ScDon10..RepTagConfig ON ScDon10..RepTableConfig.IdRepTable = ScDon10..RepTagConfig.IdRepTableWHERE (ScDon10..RepTableConfig.TableName = @InvolvedTable)print @ConditionInsertprint @LocalFieldif (@ConditionInsert = '0')begin if (@LocalField = '1') begin set @sql =' SELECT distinct ScDon10..RepTagConfig.IdTag as tag,ScDon10..cprConfiguration.[Name]as tagname,ScDon10..RepTagConfig.FieldNumber as NrTubo, eVisionDonalsid..'+@InvolvedTable+'.'+@FieldValue+' as FieldValue, ScDon10..Heat.IdHeat as IdHeat, ScDon10..Heat.HeatNumber as HeatNumber, ScDon10..Heat.OrderNumber as OrderNumber FROM ScDon10..RepTableConfig INNER JOIN ScDon10..RepTagConfig ON ScDon10..RepTableConfig.IdRepTable = ScDon10..RepTagConfig.IdRepTable INNER JOIN ScDon10..cprConfiguration ON ScDon10..RepTagConfig.IdTag = ScDon10..cprConfiguration.IdTag INNER JOIN ScDon10..crpTagType ON ScDon10..cprConfiguration.IdTagType = ScDon10..crpTagType.IdTagType cross join Heat cross Join eVisionDonalsid..Replications inner join eVisionDonalsid..Heats on eVisionDonalsid..Replications.IdRegister = eVisionDonalsid..Heats.HeatID INNER JOIN eVisionDonalsid..'+@InvolvedTable+' ON eVisionDonalsid..Heats.HeatID = eVisionDonalsid..'+@InvolvedTable+'.HeatID WHERE (ScDon10..RepTableConfig.TableName = '''+@InvolvedTable+''')and (eVisionDonalsid..Heats.HeatID ='''+@IdRegister+''')and (ScDon10..RepTagConfig.FieldValue = '''+@FieldValue+''')and (ScDon10..Heat.HeatNumber= '''+@HeatNumber+''')' end -- tabella diretta tag indiretti else begin --select sbagliata da correggere set @sql =' SELECT distinct ScDon10..RepTagConfig.IdTag as tag,ScDon10..cprConfiguration.[Name]as tagname,ScDon10..RepTagConfig.FieldNumber as NrTubo, eVisionDonalsid..'+@JointTable+'.'+@FieldValue+' as FieldValue, ScDon10..Heat.IdHeat as IdHeat, ScDon10..Heat.HeatNumber as HeatNumber, ScDon10..Heat.OrderNumber as OrderNumber FROM ScDon10..RepTableConfig INNER JOIN ScDon10..RepTagConfig ON ScDon10..RepTableConfig.IdRepTable = ScDon10..RepTagConfig.IdRepTable INNER JOIN ScDon10..cprConfiguration ON ScDon10..RepTagConfig.IdTag = ScDon10..cprConfiguration.IdTag INNER JOIN ScDon10..crpTagType ON ScDon10..cprConfiguration.IdTagType = ScDon10..crpTagType.IdTagType cross join Heat CROSS JOIN '+@InvolvedTable+' INNER JOIN '+@tagJointTable+' ON '+@InvolvedTable+'.'+@tagLocalJointField+' = '+@tagJointTable+'. '+@tagLocalJointField+' WHERE (ScDon10..RepTableConfig.TableName = '''+@InvolvedTable+''')and (ScDon10..RepTagConfig.FieldValue = '''+@FieldValue+''') and (ScDon10..Heat.HeatNumber= '''+@HeatNumber+''')' end endWhy i don't recive a @sql value????Thank's |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-05 : 09:26:34
|
[code]declare @sql nvarchar(4000), @InvolvedTable nvarchar (100), @HeatNumber char(10), @IdRegister nvarchar(50),@sql1 nvarchar(4000), @FieldValue nvarchar (50)set @InvolvedTable = 'Heats'set @HeatNumber ='761501'set @IdRegister = '1329'set @sql = ''set @FieldValue = 'Name'execute qrySQl @InvolvedTable, @HeatNumber, @IdRegister, @sql = @sql output , @FieldValueprint (@sql)[/code]Chirag |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-05 : 09:27:07
|
You have two options. Either you do a SELECT @sql, or you make the @sql parameter OUTPUT when calling the SP, as this:declare @sql nvarchar(4000), @InvolvedTable nvarchar (100), @HeatNumber char(10), @IdRegister nvarchar(50),@sql1 nvarchar(4000), @FieldValue nvarchar (50)set @InvolvedTable = 'Heats'set @HeatNumber ='761501'set @IdRegister = '1329'set @sql = ''set @FieldValue = 'Name'execute qrySQl @InvolvedTable, @HeatNumber, @IdRegister, @sql out, @FieldValueprint (@sql)Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-05 : 09:27:33
|
[code]execute qrySQl @InvolvedTable, @HeatNumber, @IdRegister, @sql OUTPUT, @FieldValue[/code] KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-05 : 09:28:44
|
The Yak will be here soon... Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-05 : 09:28:45
|
Yaks KH |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-05 : 10:10:11
|
Still wondering about how to pronounciate YaksPeter LarssonHelsingborg, Sweden |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-09-05 : 10:13:56
|
pRoBlEm sOlVedIf it is that easy, everybody will be doing it |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-09-05 : 11:04:32
|
quote: Originally posted by OBINNA_EKE pRoBlEm sOlVedIf it is that easy, everybody will be doing it
Now you need to solve lower,upper case problem MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|