InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:29:08 GMT20http://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxThe last line in the dynamic sql is highly suspect.[code="sql"]'where act.uniqueincluster between select min('+@uniqueincluster+') from #temp1 and select max('+@uniqueincluster+') from #temp1'[/code]It doesn't make sense to have the variable in there. I think you mean [code="sql"]'where act.uniqueincluster between (select min(uniqueincluster) from #temp1) and (select max(uniqueincluster) from #temp1)'[/code]but I can't be sure of the logic of course.Definitely heed the warning of not trying this in production. It will not work. There are only a handful of tables here to mock up for a test, it shouldn't take long.edit: speelingTue, 04 Sep 2012 15:28:33 GMTtoddasdhttp://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxWhat your narrative describes is a 1950's magnetic tape or punch card system, NOT RDBMS at all. You are just using SQL to do this. Your temp tables are scratch tables. The cursors are based ON the old IBM tape file routines in COBOL. Literally; we read the manuals to get ideas for the later cursor directional read syntax. You have an ORDER BY clause on the first INSERT INTO statement. Sorting the insertion order of records on a magnetic tape makes sense, but this is SQL. The rows of a table have no ordering by definition. This is not a tricky part of the language; this is basics usually covered in the first 2-3 lessons. I see you have a table for “Active_Jobs” and another for “Done_Jobs” but not table for “Jobs” because punch card sorters would have put cards into two decks based on a flag punched into each unit record. You need to read ISO-11179 or any book on basic data modeling. A procedure is named “<verb>_<object>”, and the data element names are also wrong. You have verbal phrases, adjectives with a noun, etc. Think about “@last_submitted” and the obvious question “last submitted WHAT?” The rest of the code is even more vague. I see you still write each data element on one punch card with the comma at the front; we did that so we could re-arrange the deck. Do you really expect to get BIGINT sized data? You really get more applications than there are electrons in the Universe? You use both DECIMAL(18,0) and NUMERIC(18,0) in the parameter list; do you know the differences? If you will post DDL, as required by minimal netiquette, maybe some can help you replace this with a real RDBMS that follows basic industry standards. I do not think this is worth kludging. Mon, 03 Sep 2012 15:35:51 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxThanks For the reply Gail. I will test it before I move it to production.Mon, 03 Sep 2012 13:25:18 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxUntested code in production... That's just asking for trouble. Is that valid? Yes, as far as I can tell.Will it work correctly and perform adequately? No idea.Ask your DBA to transfer some relevant data down to test so that you can test this. Or use a data generator tool to mock up some data.Mon, 03 Sep 2012 11:55:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxThanks for the reply Gail. . Due to non existent data in our test environments, we dont have a valid environment to use this SP. Hence I will have to use it directly in my production environment. I want to take precautions before running it in our production systems.Mon, 03 Sep 2012 11:30:10 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxWhat errors do you get when you run the CREATE PROCEDURE?Mon, 03 Sep 2012 10:59:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1353599-392-1.aspxHello,I have written a stored proc that creates temp tables and uses a table cursor and then drops the temp tables. I am not sure if this is all valid inside a stored proc. Any inputs are highly appreciated.code:[code="sql"]USE [master]GO/****** Object: StoredProcedure [dbo].[test] Script Date: 09/03/2012 11:48:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[test] @LastTransactionId Decimal(18,0),@LastSubmitted numeric(18,0) AS SET NOCOUNT ON create table #temp1 ( PracticeName varchar(250) not null, Uniqueincluster decimal(18,0) not null, AppCount bigint not null, ProvCount bigint not null, BillableUnits bigint not null) create table #temp2 ( BillingMode nvarchar(50), QueuedAtDate datetime, JobUnique nvarchar(50), [Status] nvarchar(50), ProductId nvarchar(75), TransactionDate datetime, TransactionID nvarchar(50) not null, AccountId int, SubAccountId int, EntityName nvarchar(100), ApplicationDescription nvarchar(100), OwnerName nvarchar(100), TransactionType nvarchar(1), TransactionStatus nvarchar(10), BillableUnits int) insert into #temp1 select act.PracticeName, act.uniqueincluster, act.CallCount as "App Count", SUM(pct.CallCount) as "Prov Count", act.CallCount - SUM(pct.CallCount) as BillableUnits from CallClusterVARASP.dbo.ApplicationCallTally act right join CallClusterVARASP.dbo.ProviderCallTally pct on pct.UniqueInCluster = act.UniqueInCluster where act.uniqueincluster between @LastSubmitted and @LastTransactionId group by act.uniqueincluster, act.CallCount, act.PracticeName having act.CallCount <> SUM(pct.CallCount) and SUM(pct.callcount) > 0 order by 1,2 DECLARE @billableunits1 varchar(10)DECLARE @uniqueincluster1 varchar(10)DECLARE @sql NVARCHAR(4000)DECLARE TableCursor CURSOR FORSELECT billableunits,uniqueincluster FROM #temp1 OPEN TableCursorFETCH NEXT FROM TableCursor INTO @billableunits1,@uniqueincluster1WHILE @@FETCH_STATUS = 0BEGINSET @sql = 'insert into #temp2select distinct''Submitted'' as BillingMode, getdate() as QueuedAtDate, act.jobUnique as JobUnique, ''Queued'' as Status, ''PT'' as ProductId, IsNull(dj.JobSubmittedAt, aj.JobSubmittedAt) as TransactionDate, act.UniqueInCluster as TransactionID, IsNull(dj.AccountNumber, aj.AccountNumber) as AccountId, au.ID as SubAccountId, act.PracticeName as EntityName, act.Application as ApplicationDescription, act.PracticeName as OwnerName, ''P'' as TransactionType, null as TransactionStatus,'+@billableunits1+' as BillableUnits from ApplicationCallTally act left outer join ActiveJobs aj on aj.UniqueInCluster = act.UniqueInCluster and aj.JobUnique = aj.JobUniqueleft outer join DoneJobs dj on dj.UniqueInCluster = act.UniqueInCluster and dj.JobUnique = dj.JobUniqueleft outer join EzMedCore.dbo.AccountUser au on au.AccountNumber = IsNull(dj.AccountNumber, aj.AccountNumber) and act.AspUserID = au.IDwhere act.uniqueincluster between select min('+@uniqueincluster+') from #temp1 and select max('+@uniqueincluster+') from #temp1'--PRINT(@sql)EXEC (@sql)FETCH NEXT FROM TableCursor INTO @billableunits1,@uniqueincluster1ENDCLOSE TableCursorDEALLOCATE TableCursorinsert into HAM_SubmittedQueueselect * from #temp2drop table #temp1drop table #temp2GO[/code]Mon, 03 Sep 2012 10:40:16 GMTSapen