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
 return a string value

Author  Topic 

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-05 : 09:23:30
hello, I have written this stored procedures


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, @FieldValue

print (@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


)
as

declare @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.RemoteJointField
FROM ScDon10..RepTableConfig INNER JOIN ScDon10..RepTagConfig ON ScDon10..RepTableConfig.IdRepTable = ScDon10..RepTagConfig.IdRepTable
WHERE (ScDon10..RepTableConfig.TableName = @InvolvedTable)



print @ConditionInsert
print @LocalField

if (@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


end


Why 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 , @FieldValue

print (@sql)
[/code]

Chirag
Go to Top of Page

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, @FieldValue

print (@sql)




Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 09:28:44
The Yak will be here soon...

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-05 : 09:28:45


Yaks

KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-05 : 10:06:54
Also refer
http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Peso, again you forgot it

Madhivanan

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 10:10:11
Still wondering about how to pronounciate Yaks


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-09-05 : 10:13:56
pRoBlEm sOlVed

If it is that easy, everybody will be doing it
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-05 : 11:04:32
quote:
Originally posted by OBINNA_EKE

pRoBlEm sOlVed

If it is that easy, everybody will be doing it


Now you need to solve lower,upper case problem

Madhivanan

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

- Advertisement -