Issue with OUT Param

Question

Issue with OUT Param

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
April 6, 2017 at 11:07 am

#323606

I want to call this dbo.Validate procedue in other proc, ; I want t set OUT param for @count; I want to return this value. How to set it out.
Alter proceudre dbo.validate
As
begin
set no count on;
declare
@Count int = 0
          INSERT INTO dbo.TCRE
(
    ID
   ,EmpName
                        ,EmpNum)
  SELECT
    ID
   ,EmpName
                        ,EmpNum
FROM dbo.Employee EM
   inner join temp.STudent ST
   on EM.ID = ST.ID

SET @Count = @@ROWCOUNT
IF ( @Count <> 0 )
BEGIN
   INSERT INTO dbo.Log
      (Error, ErrorLine,Remark,CreatedDate)
      VALUES
      ( 0, NULL,'Mismatch',getdate())
END
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

your stored procedure won't even compile. Besides, there's no declaration of your output parameter. It's declared just like an input parameter, but it's got an OUTPUT tag on it.

https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

I got t But ho I can assign entire Insert script to @Count variable ? so it returns as out.

Alter proceudre dbo.validate

@Count INT OUTPUT

As
begin
set no count on;
INSERT INTO dbo.TCRE
(
ID
,EmpName
,EmpNum)
SELECT
ID
,EmpName
,EmpNum
FROM dbo.Employee EM
inner join temp.STudent ST
on EM.ID = ST.ID

SET @Count = @@ROWCOUNT
IF ( @Count <> 0 )
BEGIN
INSERT INTO dbo.Log
(Error, ErrorLine,Remark,CreatedDate)
VALUES
( 0, NULL,'Mismatch',getdate())
END

END

Lowell SSC Guru Points: 323518 More actions · Answer 3

CREATE PROCEDURE outputParamTest(@Count int OUTPUT) AS BEGIN --do something that affects some number of rows. SELECT * INTO #temp FROM sys.tables SELECT @Count = @@ROWCOUNT

END

GO

--prove this works. here's how I can get the value back,. DECLARE @Results int EXEC outputParamTest @Count = @Results OUTPUT SELECT @Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

It got worked, TY

Quick question - Do I need to keep this If condtion inside the procedure or do I need to keep it after calling this procedure from another proc?

IF ( @Count <> 0 )
BEGIN
INSERT INTO dbo.Log
(Error, ErrorLine,Remark,CreatedDate)
VALUES
( 0, NULL,'Mismatch',getdate())
END

Lowell SSC Guru Points: 323518 More actions · Answer 5

i could not tell you; the logic looks strange.
if rows get inserted, that is considered an error? IF ( @Count <> 0 ) INSERT INTO dbo.Log(Error....

designwise, the procedure itself should do everything. if there is a logical test where something should occur or not occur, the procedure should either log the error, rollback the transaciton, or do some sort of logic....the detection of an error based on count of rows belong sinside the procedure, i think, and not on something that calls the procedure.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!