How to Return A Variable from sp

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

General SQL Server Forums

New to SQL Server Programming

How to Return A Variable from sp

Author

Topic

prabhakar_1432
Starting Member

5 Posts

Posted - 2010-09-24 : 04:55:19

Hi,
I am doing some update query in Stored Procedure, I am taking entire update query into a @STR nvarchar(4000) & another query again to update for the different table. My problem is 1st query is being updated then it is going to the 2nd query. Wheather 1st query is not succesful then doesn't goes to 2nd query. What should i do?

ALTER PROCEDURE [dbo].[abc]
@TotalcolumnName nvarchar(500),
@Totalcolvalue nvarchar(500),
--@PURSRNO nvarchar(40),
@trtyp nvarchar(15),
@trtyp_sr nvarchar(14),
@docno nvarchar(8),
@docdt smalldatetime,
@document nvarchar(100),
@partycd nvarchar(6),
@billamt money,
@rndoff money,
@totqty money,
@totgramt money,
@totdiscamt money,
@totnetamt money,
@taxper real,
@rdocno nvarchar(20),
@shipcode nvarchar(4),
@terms nvarchar(300),
@crdays int,
@duedt smalldatetime,
@delidate smalldatetime,
@Qref nvarchar(50),
@Qdt smalldatetime,
@narration nvarchar(300)

as
DECLARE @SQLStatement nvarchar(MAX);

if ltrim(@TotalcolumnName)=''
begin
SET @SQLStatement = 'Update Purhead set docdt='+ QUOTENAME(@docdt,'''') +', trtyp='+ QUOTENAME(@trtyp,'''') +', TRTYP_SR='+ QUOTENAME(@trtyp_sr,'''') +', docno='+ QUOTENAME(@docno,'''') +',DOCUMENTS='+ QUOTENAME(@document,'''') +', partycd='+ QUOTENAME(@partycd,'''') +',billamt='+ QUOTENAME(@billamt,'''') +', rndoff='+ QUOTENAME(@rndoff,'''') +', totqty='+ QUOTENAME(@totqty,'''') +', totgramt='+ QUOTENAME(@totgramt,'''') +',TOTTDAMT='+ QUOTENAME(@totdiscamt,'''') +', totnetamt='+ QUOTENAME(@totnetamt,'''') +', taxper='+ QUOTENAME(@taxper,'''') +',rdocno='+ QUOTENAME(@rdocno,'''') +',SHIPCODE='+ QUOTENAME(@shipcode,'''') +',crdays='+ QUOTENAME(@crdays,'''') +',duedt='+ QUOTENAME(@duedt,'''') +',DELIDATE='+ QUOTENAME(@delidate,'''') +', Quoteref='+ QUOTENAME(@Qref,'''') +', Quotedt='+ QUOTENAME(@Qdt,'''') +' where TRTYP_SR='+ QUOTENAME(@trtyp_sr,'''') +'' ;
end
else
begin
SET @SQLStatement = 'Update Purhead set docdt='+ QUOTENAME(@docdt,'''') +', trtyp='+ QUOTENAME(@trtyp,'''') +', TRTYP_SR='+ QUOTENAME(@trtyp_sr,'''') +', docno='+ QUOTENAME(@docno,'''') +',DOCUMENTS='+ QUOTENAME(@document,'''') +', partycd='+ QUOTENAME(@partycd,'''') +',billamt='+ QUOTENAME(@billamt,'''') +', rndoff='+ QUOTENAME(@rndoff,'''') +', totqty='+ QUOTENAME(@totqty,'''') +', totgramt='+ QUOTENAME(@totgramt,'''') +',TOTTDAMT='+ QUOTENAME(@totdiscamt,'''') +', totnetamt='+ QUOTENAME(@totnetamt,'''') +', taxper='+ QUOTENAME(@taxper,'''') +',rdocno='+ QUOTENAME(@rdocno,'''') +',SHIPCODE='+ QUOTENAME(@shipcode,'''') +',crdays='+ QUOTENAME(@crdays,'''') +',duedt='+ QUOTENAME(@duedt,'''') +',DELIDATE='+ QUOTENAME(@delidate,'''') +', Quoteref='+ QUOTENAME(@Qref,'''') +', Quotedt='+ QUOTENAME(@Qdt,'''') +', '+ @TotalcolumnName +' where TRTYP_SR='+ QUOTENAME(@trtyp_sr,'''') +'' ;
end

EXEC sp_executesql @SQLStatement;

--if @SQLStatement = 0
-- begin
Update Purhead set Narra=@narration, terms=@terms where TRTYP_SR= @trtyp_sr;
--end

If @SQLStatement is successful then it goes to next updatation, If @SQLStatement is failed doesn't goes to 2nd query.

Please solve this problem.

Pborade

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-24 : 05:14:09

You are not very clear.
Are there two statements in one SP?
Can you show your code and describe what should happen?

No, you're never too old to Yak'n'Roll if you're too young to die.