print output for sproc global variable in QA

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

SQL Server 2000 Forums

SQL Server Development (2000)

print output for sproc global variable in QA

Author

Topic

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 17:06:04

Hi everyone,

Please bare with me and my lack of knowledge and understanding of terms and so-forth, I am only a co-op student in the learning proccess!!

I am working on an existing sproc so that when you execute it in QA it will return the @@rowcount for the table

In the sproc i have @Entries = @@rowcount (@entries is a declared Output)

then in the QA is:

*************************************
declare @Status int,
@Entries int

Exec spSmXferQueue_InsWebPageGrp 'test2', 'A', 'admin', 1, 1, 0, '1', 1, @Status Output, @Entries Output

print @Entries
**************************************

for some reason this always returns the value 20, although this is not the case, the passed values are just there to fit the requirments of the table and status declares if it worked or not

Please Help! i gotta look good for the developers =)

Mike

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 17:13:11

Please show us the code of spSmXferQueue...

You probably aren't setting @Entries directly after the statement that you want the rowcount for.

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 17:15:04

if exists (select * from sysobjects where id = object_id('dbo.spSmXferQueue_InsWebPageGrp') and sysstat & 0xf = 4)
drop procedure spSmXferQueue_InsWebPageGrp
GO

CREATE PROCEDURE spSmXferQueue_InsWebPageGrp
@SiteCode varchar(15),
@JobType char(1) = null,
@UserID varchar(25) = null,
@XferCmd smallint,
@TargetType smallint, -- 1=Production, 2=FullPreview, 3=All
@UsePreviewData smallint, -- 1=Yes; 0=No
@PageIdList varchar(4000) = null,
@LangId int = null,
-- output parms
@Status int = null Output,
@Entries int = null Output
AS
Declare @SiteID numeric(18,0),
@zInsCmd varchar(4000)

Set Nocount On
Select @Status = 1 -- expect failure

-- validate SiteCode parameter passed in
Select top 1 @SiteID = s.SiteID
from wxMaster.dbo.SiteDetails s
where s.SiteCode = @SiteCode
and isnull(RemovedDate,'1/1/1900') = '1/1/1900'

if isnull(@SiteID,0) = 0
begin
RaisError ('spSmXferQueue_InsWebPageGrp: Active Site Code not found: %s',
16,1,@SiteCode)
Return
end

-- create new job in wxMaster schema
Select @zInsCmd =
'Insert into wxMaster.dbo.XferQueue (SiteID, JobType, ' +
' Status, ErrMsg, CreatedDate, CreatedBy, ' +
' LangPrefix, SiteRootSuffix, XferCmd, ' +
' SrcFolderName, SrcFileName, DstFolderName, DstFileName,' +
' SSPubOrder, SSPubName, PlatformType, ' +
' Staging, StagingMethod, StagingTime, StagingTimeFreq,' +
' PreviewType, PreviewFolder, ' +
' SSSrvOrder, SSSrvName, ' +
' PubMethod, PubTime, PubTimeFreq, ' +
' Protocol, Passive, SiteMethod, ' +
' SiteAddr, SiteHomeHttp, ' +
' SiteXferAddr, SiteXferHome, ' +
' Port, PreviewAddr, smUser, smPassword, UpdateHyperLnk, ' +
' AltServer, AltSiteXferAddr, AltSiteXferHome, ' +
' AltPort, AltUser, AltPassword, ' +
' TzOffset, AutoDay, PageId, LangId, TargetType, UsePreviewData ' +
')' +
'Select ' + cast(@SiteId as varchar) + ', ' +
' ''' + isnull(@JobType,'') + ''', ' +
' 0, '''', GetUTCdate(), ''' + isnull(@UserId,'') + ''', ' +
' '''', '''', ''' + cast(@XferCmd as varchar) + ''', ' +
' '''','''','''','''', 1,'''', 1, 1, 1, 1, 0, 1, '''', 1, ' +
' '''', ''2'', 1, 1, 1, 1, 1, '''', '''', '''', '''', 1, '''', ' +
' '''', '''', 1, 1, '''', '''', 1, '''', '''', 0, 1 ' +
' , Id, ' + cast(@LangId as varchar) + ', ' + cast(@TargetType as varchar) + ', ' +
cast(@UsePreviewData as varchar) +
' from SmPages ' +
' where languageId = ' + cast(@LangId as varchar) +
' and Id in (' + @PageIdList + ')'

-- execute it
exec(@zInsCmd)
Select @Entries = @@ROWCOUNT

if @@error > 0
Begin
RaisError ('spSmXferQueue_InsWebPageGrp: Error inserting new XferJob record',16,1)
Return
End

Select @Status = 0 -- success

go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 17:18:38

So run this in QA (you'll need to tweak a bit to run since I didn't declare everything):



declare @zInsCmd varchar(4000)

Select @zInsCmd = 
'Insert into wxMaster.dbo.XferQueue (SiteID, JobType, ' +
' Status, ErrMsg, CreatedDate, CreatedBy, ' +
' LangPrefix, SiteRootSuffix, XferCmd, ' +
' SrcFolderName, SrcFileName, DstFolderName, DstFileName,' +
' SSPubOrder, SSPubName, PlatformType, ' +
' Staging, StagingMethod, StagingTime, StagingTimeFreq,' +
' PreviewType, PreviewFolder, ' +
' SSSrvOrder, SSSrvName, ' +
' PubMethod, PubTime, PubTimeFreq, ' +
' Protocol, Passive, SiteMethod, ' +
' SiteAddr, SiteHomeHttp, ' +
' SiteXferAddr, SiteXferHome, ' +
' Port, PreviewAddr, smUser, smPassword, UpdateHyperLnk, ' +
' AltServer, AltSiteXferAddr, AltSiteXferHome, ' +
' AltPort, AltUser, AltPassword, ' +
' TzOffset, AutoDay, PageId, LangId, TargetType, UsePreviewData ' +
')' +
'Select ' + cast(@SiteId as varchar) + ', ' +
' ''' + isnull(@JobType,'') + ''', ' +
' 0, '''', GetUTCdate(), ''' + isnull(@UserId,'') + ''', ' +
' '''', '''', ''' + cast(@XferCmd as varchar) + ''', ' +
' '''','''','''','''', 1,'''', 1, 1, 1, 1, 0, 1, '''', 1, ' +
' '''', ''2'', 1, 1, 1, 1, 1, '''', '''', '''', '''', 1, '''', ' +
' '''', '''', 1, 1, '''', '''', 1, '''', '''', 0, 1 ' +
' , Id, ' + cast(@LangId as varchar) + ', ' + cast(@TargetType as varchar) + ', ' + 
cast(@UsePreviewData as varchar) +
' from SmPages ' +
' where languageId = ' + cast(@LangId as varchar) +
' and Id in (' + @PageIdList + ')'

exec (@zInsCmd)

print @@ROWCOUNT

How many rows did it say it updated?

BTW, the code looks correct. How do you know that 20 rows weren't in fact the rowcount?

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 17:21:00

OK thanks i'll give it a go

I knew it wasnt because i did a select statement to view the rows, as well as delete them and start fresh with no avail

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 17:29:26

Ok it works, but it's returning the value '0'

is it not possible to exec the sproc? or are you just trying to determine if its working first? (troubleshooting style

)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 17:34:49

Are you asking if we can execute the sproc? If so, then no we can't. We don't have the tables involved or data.

Run a delete prior to the code I posted. Delete from wxMaster.dbo.XferQueue. Then after the code executes, @@ROWCOUNT will equal select count(*) from wxMaster.dbo.XferQueue.

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 17:49:23

No no no, of course i wouldnt ask you to execute it!

I was refering to the fact that you werent using "execute [sprocName]...."

and instead selecting a specific part of the code

anyways i'll try deleting the records and see what happens

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 17:53:13

Ok, i deleted the rows from wxmaster.dbo.xferqueue and because the code you provided didnt execute the sproc, a record wasn't created. I then used the query i had original (and posted above) used to create an instance. I then ran your code and it still returns the value '0'!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 17:53:39

Ah yes, I typically strip out the code from the stored procedure to make it easier to troubleshoot. That way I can add all kinds of print statements or whatever I need without modifying the stored proc. Then when I'm done, I make whatever necessary changes that I need to in the sproc.

BTW, the @@ERROR section isn't going to work. By running @Entries = @@ROWCOUNT, @@ERROR now contains the error number of that statement and not of the insert statement. You need to put put @@ROWCONT and @@ERROR into variables in one call:

SELECT @Entries = @@ROWCOUNT, @Err = @@ERROR

Then:

IF @Err > 0...

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 17:59:51

The code I provided was supposed to do an INSERT. Can you remove the dynamic sql in it? Just run the select statement without it being dynamic.

Here's an example of what you are trying to do:

CREATE PROC SomeProc
(@Entries int OUTPUT)
AS

SELECT * FROM sysobjects
WHERE type = 'U'

SET @Entries = @@ROWCOUNT

RETURN 0

GO

----
DECLARE @Entries INT

EXEC SomeProc @Entries OUTPUT

PRINT @Entries

It's just hard to help you out with the dynamic sql in the sproc. If @@ROWCOUNT is returning 0, then it is due to the select statement not finding any rows to match the criteria. So I would suggest running just the select statement in QA without dynamic sql. Hard code everything in the select.

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 18:05:46

OK, this is why i appoligized at the begginning =)

I'm unaware of what is dynamic or not...if you are busy i can read up on it instead of wasting your time!

Yes that seems to capture the basic concept of what i'm trying to do, so should i just change SELECT with INSERT?

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 18:06:15

BTW, thanks for the Error correction!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 18:08:09

Dynamic sql is when you build the query:

DECLARE @SQL VARCHAR(4000)
DECLARE @TableName SYSNAME

SET @TableName = 'sysobjects'

SET @SQL = 'SELECT * FROM ' + @TableName

EXEC (SQL)

For troubleshooting, you often put PRINT @SQL to see the dynamically create query. My example is lame though, but it shows what dynamic sql is. So in your sproc, I would put PRINT @zInsCmd before the EXEC. Grab the query and put it in another window. Remove the INSERT part and just execute the select.

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 18:08:19

Are you referring to the '' blanks insterted into the table, when referring to 'dynamic'?

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 18:10:38

Ok, i'll give it a go

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 18:44:40

OK, i talked to a developer and he was doubtful on the whole rowcount thing, so i'am now creating a temporary table in memory and will use count to determine the rowcount before inserting it into xferqueue

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 18:45:46

@@ROWCOUNT will work. Not sure why the developer doesn't think so. It's the whole purpose of @@ROWCOUNT.

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 18:50:14

Ya i know, i'm going to go back and figure it out

In the mean time he needs this done. So, im using this outline for the temp tables, any suggestions on the best way to implement it?

insert into @t
select Form_Name, [ID]
from Forms

select t.Name as Form, form_fields.Display_as as Field
from @t as t inner join form_fields
on t.Primarykey = form_fields.formID

I also created a zSelCmd for the temp table above

select @zSelCmd =
' select @Entries = count(*) ' +
' from SmPages ' +
' where languageId = ' + cast(@LangId as varchar) +
' and Id in (' + @PageIdList + ')'
exec(@zSelCmd)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 18:53:44

The best way would be to not use dynamic sql. Dynamic sql is bad for security and performance. He should be using this method for the Id in (' + @PageIdList + ')':

http://www.sqlteam.com/item.asp?ItemID=11499

Could you explain what he wants?

Tara

slimatic
Starting Member

13 Posts

Posted - 2004-06-28 : 19:01:47

I dont think im in the position to recommend the way he codes you know?

But basically hes creating a temporary table in memory so that he can do a select count(*) of all the rows created. Once that number is found it passes the rows to the insert command to be put where they are supposed to be (xferqueue)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources