Does SETROWCOUNT 10 apply to only the sp in which it used or outside it also?

I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.

Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?

The option SET ROWCOUNT affects only the current connection session. It does not transfer from one connection to another in a connection pooling environment. Nor does the scope exceed stored procedure boundaries

ifobject_id('usp1')isnot nulldrop proc usp1gocreate proc usp1asbegin set rowcount 10select *from sys.objectsendgoexec usp1goselect *from sys.objectsgoifobject_id('usp1')isnot nulldrop proc usp1go

Does anyone have an ForEachColumn Procedure?

Hi

Does anyone have an ForEachColumn Procedure?
(a bit like the MSforeachdb and MSforeachtable sps)

This should be quite generic for anyone working with real life data coming in from other systems.

I need to fill out the rows with missing values in historic records from the previous current record.
(sometimes even from a previous historic record)
And sometimes the current record doesn't have data in the field, so I shouldn't get the data from current -1.

Can solve most of the complexities of the filling out part.

But it needs to be applied for (most of) my 57 columns,and then there is even some differently formatted data just beyond the horizon, so maybe I should try to get a more generic applicable solution.

Did anyone try a ForEachColumnExcept procedure

CREATE PROCEDURE ForEachColumnExcept
@.TableName varchar(200)
, @.ExceptionList varchar(8000) = '' -- the few ones to exclude
, @.DelimterInExceptionlist varchar(10) = ','
AS
...
END

the difficulty here is getting the columns from the databse's data-dictionary
)haven't done that before, but I think I'll find that out.

Or even:

CREATE PROCEDURE ForEachColumnInList
@.TableName varchar(200)
, @.ColumnList varchar(8000) -- all the ones to include
, @.DelimterInColumnList varchar(10) = ','
AS
...
END

Cheers

Drionot sure what u finally want to do with the column list, this will however generate a column list & data type based on params u declared

set @.ExceptionList = ','+ @.ExceptionList + ','
select Column_name,Data_Type from information_schema.columns
where table_name=@.TableName and column_name not in (case when charindex(','+column_name+',',@.ExceptionList) > 0 then column_name else '' end)

Documenting large numbers of SPs

Hi All,

I have a large number of SPs that I would like to be able to document and provide this documentation to prospective clients. That is, provide them enough information without giving them the source code for the procedures.

I have found that all the parameters are in the sys.parameters table.

But I was wondering. Are the fields that are sent back out of an SP captured and recorded somewhere in the SQL Server catalog?

Is there an easy way to find out what fields are coming out of an SP?

Thanks in Advance

Peter

You may be better served exploring one of the third party documentation tools.

I like ApexSQL's Doc tool.

You can download a fully functional eval version.

|||

Hi Arnie,

thanks for the tip...I'll take a look..

Peter

Documenting existing stored procedures

Does anyone have any scripts, or know of any tools, that can scan through all of the SPs that I have inherited, documenting details?
ThxThis will retrieve the name and text of all the stored procedures.

SELECT a.name, b.text
FROM sysobjects a INNER JOIN syscomments b ON
a.id = b.id
WHERE
a.xtype = 'P'

This will retrieve the input parameters for all stored procedures in a database:

SELECT a.name, b.*
FROM sysobjects a INNER JOIN syscolumns b ON
a.id = b.id
WHERE
a.xtype = 'P'

What else are you looking to do?|||thanks! :o :o|||Did you use Enterprise Manager to script out the stored procedures and the like?

You can use this to Search your database (http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx)

Good Luck