February 26, 2009 at 9:53 am
We have about 4,000 tables that need to be dropped if the record count is 0. I have come up with something very basic, but for the life of me can't get it to work. It will work outside of the stored proc, but as soon as I call the stored proc it fails with. I know there are probably better ways to accomplish this, but I would love to know what I am missing syntax wise.
SET @reccount =(SELECT COUNT(1) FROM Q1257855)
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@reccount'.
CREATE PROCEDURE usp_PLQuotes @policyid varchar(15)
AS
DECLARE @reccount AS int
DECLARE @sql AS nvarchar(1000)
SET @reccount = 0
SET @sql = 'SET @reccount '
SET @sql = @sql + '(SELECT COUNT(1) FROM '
SET @sql = @sql + @policyid + ')'
PRINT @sql
EXEC sp_executesql @sql
PRINT @reccount
-- IF @reccount = 0
-- Begin
-- SET @sqlstmt = 'DROP TABLE ' & @policyid
-- EXEC sqlstmt
--
--
-- end
--
--
GO
February 26, 2009 at 9:55 am
You can't declare a variable in the calling proc, then use it in the dynamic SQL. sp_executesql allows for output parameters. Set up one of those, you'll be able to get what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 11:07 pm
Use this as a source of rowcounts, instead of all that RBAR and counting...
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
If you can't figure it out, post back...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2009 at 11:51 am
You could try this:
CREATE PROCEDURE usp_PLQuotes @policyid varchar(15)
AS
begin
DECLARE @reccount AS int
DECLARE @sql AS nvarchar(1000)
SET @reccount = 0
SET @sql = 'declare @reccount int;SET @reccount '
SET @sql = @sql + '(SELECT COUNT(1) FROM '
SET @sql = @sql + @policyid + ')'
PRINT @sql
EXEC sp_executesql @sql
PRINT @reccount
......
......
end
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply