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
 return a string value
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/05/2006 :  09:23:30  Show Profile
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/05/2006 :  09:26:34  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message

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)


Chirag

Edited by - chiragkhabaria on 09/05/2006 09:26:54
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 09/05/2006 :  09:27:07  Show Profile  Visit SwePeso's Homepage
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)

Singapore
17587 Posts

Posted - 09/05/2006 :  09:27:33  Show Profile
execute qrySQl @InvolvedTable, @HeatNumber, @IdRegister, @sql OUTPUT, @FieldValue



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 09/05/2006 :  09:28:44  Show Profile  Visit SwePeso's Homepage
The Yak will be here soon...

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 09/05/2006 :  09:28:45  Show Profile


Yaks

KH


Edited by - khtan on 09/05/2006 09:29:21
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/05/2006 :  10:06:54  Show Profile  Send madhivanan a Yahoo! Message
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

Sweden
30117 Posts

Posted - 09/05/2006 :  10:10:11  Show Profile  Visit SwePeso's Homepage
Still wondering about how to pronounciate Yaks


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 09/05/2006 :  10:13:56  Show Profile
pRoBlEm sOlVed

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/05/2006 :  11:04:32  Show Profile  Send madhivanan a Yahoo! Message
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
  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.11 seconds. Powered By: Snitz Forums 2000