Problem With Sp_Help Proc
Hi All,
I am using Sp_Help <TableName> command to get details of table.
I found out one strange this, can anybody tell me is this problem with sp_Help store Proc.
I have created table AsseType
With AssetID DataType: Int
AssetDesc DataType: nvarchar(30)
If I go to Enterprise Manager right click on it table and Click on Design, I can see size is 30.
But if I go to Query Analyzer and type Sp_Help AssetType
Then I get length as 60.
One thing which I notice is apart from nvarchar datatype for all other datatypes, it returns same field length as seen in Design mode.
I found out this on SQL7 with SP3 and SQL2K with SP1.
Any help.........
Many thanks in Advance.
Regards,
Santosh
View Complete Forum Thread with Replies
Related Forum Messages:
Problem With Sp_Help Proc
Hi All, I am using Sp_Help <TableName> command to get details of table. I found out one strange this, can anybody tell me is this problem with sp_Help store Proc. I have created table AsseType With AssetID DataType: Int AssetDesc DataType: nvarchar(30) If I go to Enterprise Manager right click on it table and Click on Design, I can see size is 30. But if I go to Query Analyzer and type Sp_Help AssetType Then I get length as 60. One thing which I notice is apart from nvarchar datatype for all other datatypes, it returns same field length as seen in Design mode. I found out this on SQL7 with SP3 and SQL2K with SP1. Any help......... Many thanks in Advance. Regards, Santosh
View Replies !
Sp_help
We use sql server 6.5. When I do an sp_help <table_name>, there is a last column called "FixedLenNullInSource". What is the purpose of the column? How does it get set to "Yes" or "No"? I am doing a select * into <new_table> from <old_table> in my code. Then I do a insert into that table. It works fine most of the time. When it works fine and I do a sp_help on the <new_table> and all columns have either "FixedLenNullinSource" as n/a or No. Suddenly when it starts working improperly (it pads the varbinary column with zeroes)I checked the table with the sp_help <New_table> command it shows the varbinary column with "FixedLenNullinSource" as Yes. Is there a way to manually make it 'No'? Any help will be appreciated
View Replies !
SP_Help
I need to write dynamic SQL statment that will delete a code from a table but before you can delete that code...I need to find all the foreign key constraints (all the tables that references this table to either update or delete from them and then delete from this table last) I know how to get this information from SP_Help but i dont know how I can do this dynamically. Please help. Emadkb
View Replies !
Sp_help Can't See My Tables
I'm trying to use sp_help to get information on my tables. I can usesp_help alone to get a list of objects (including user tables), butwhen I pass a table name as an argument I get the following errormessage:exec sp_helpexec sp_help parcelServer: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71The object 'DGM_HILLSHADE2' does not exist in database 'raster'.The table clearly exists, but sp_help fails to find it and returninfo. This is the case in a couple of my databases. I'm connected as'sa' so it seems to me it shouldn't be a permissions problem, right?D Bull
View Replies !
DB Suspect Not Show In Sp_help
Hi! I am having a problem with one of my local data bases, it shown the status of (suspect). When I try to apply solutions that I found on internet for example run: sp_resetstatus 'dbImpOfertas' it shows the message that : The databse 'dbImpOfertas' does not exist. Use sp_help to show available databases. This is the log information: 2007-11-28 00:00:03.68 spid53 Starting up database 'bdImpOfertas'. 2007-11-28 00:00:03.76 spid53 Error: 602, Severity: 21, State: 50 2007-11-28 00:00:03.76 spid53 Could not find row in sysindexes for database ID 15, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.. 2007-11-28 00:00:05.21 spid53 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_instance_regread'. 2007-11-28 00:00:05.21 spid54 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_instance_regread'.
View Replies !
HELP: Sp_help And Object Browser Report View Column Sizes Differently
Hi,I've run into a curious problem with MS SQL Server 8.0. Using sp_help andSQL Query Analyzer's object browser to view the columns returned by a view,I find that sp_help is reporting stale information.In a recent schema change, for example, someone lengthened a varchar columnfrom 15 to 50 characters. If we use sp_help to find out about a view thatdepends upon this column, it still shows up as VARCHAR(15), whereas theobject browser correctly reports it as VARCHAR(50).Dropping and recreating the view fixes the problem, but we have quite a fewviews, and dropping and re-creating all of them any time a schema change ismade is something we want to avoid. I tried using DBCC CHECKDB in hopes thatit would 'refresh' SQL Server's information, but no luck.(if you're curious as to why I don't just use the object browser instead,read boring technical details below)Has anyone seen this before? Is there some other way (other thanre-creating every view) to tell SQL Server to "refresh" it's information?Thanks!-Scott----------------------Boring Technical Information:The reason this is an issue for us (i.e., I can't just use the objectbrowser instead) is that our object model classes are built using standardmetadata query methods in Java that seem to be returning the same staleinformation that sp_help is returning. These methods are a part of thestandard JDK, so we can't easily fiddle with them. Anyway, as a result, ourobject model (at least with respect to views) may not match our currentschema!
View Replies !
Data Driven Subscription - Clear Proc Cache - Multiple Subs On Single Proc
I have a single proc for all of my subscriptions. I really didn't want to have to manage a table so I embedded everything into one proc. It works perfect except for a small problem on the setup of each sub in RM. In the proc I use a table variable for each report sub. I pass a single parameter into the proc to determine which table variable to create. The issue is that sql stores a previous table variable in the sql cache. In this case the validation is done by RM in a data driven subscription that will return the wrong variables. Currerntly the only work around that I have found is simply to comment out all other table variable, load and validate the current sub, and refresh the proc with un-commented table variables. They all run off the single proc fine it's just during setup I have this problem. This entire process works fine but I was wondering if anyone is doing anything similar. In addition, if anyone is managing their subs through a single proc. I tried WITH RECOMPILE and it didn't work.
View Replies !
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View Replies !
Determining What Are &"system&" Objects In Sp_help Or System Tables
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View Replies !
ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View Replies !
Processing The Resultset Of Another Proc From A Proc
Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000. Basically I am calling proc2 from the inside of proc1. proc2 returns 2 resultsets. I want to process these two resultsets from within proc1. If its possible , please provide sample code. thanks in advance, Alok.
View Replies !
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View Replies !
Resultset Of Proc In Another Proc
Hello,i want to use the result set from a stored proc in another stored proc, forexample:create stored procedure proc1 (@x int) asdeclare @tbl (y1 int, y2 int)insert into @tbl values(@ * @x, @x * @x * @x)select * from @tblGO--create stored procedure proc2 (@x int) asdeclare @tbl (y1 int, y2 int)while @x > 0 begininsert into @tbl select (exec proc1 @x) <-- this is my problemset @x = @x - 1endselect * from @tblGO--I know i could use output parameters. But i want to know if something ispossible with SQL-Server?thanks,Helmut
View Replies !
Calling A Proc From A Proc
I'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
Stored Proc - Calling A Remote Stored Proc
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563 This works fine on my local server: Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName This does not work (Attempting to execute a remote stored proc named 'Data_Add': Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto' When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly. Could anyone shed some light on what I need to do to get this to work? Thanks - Amos.
View Replies !
How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
Hey All, I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. Thanks in advance. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [SampleStoredProc] @Var1 varchar(20), @Var2 varchar(3), @Var3 varchar(2) = 'Dummy' As declare @selectlist varchar(5000) declare @tableBuild varchar(1000) declare @FieldName varchar(50) declare @FieldSelect varchar(500) declare @FieldTitle varchar(50) declare @TableName varchar(50) declare @holdTable varchar(50) declare @title varchar(50) declare @holdTitle varchar(50) declare @PageName varchar(50) declare @sequence varchar(100) declare @extraCriteria varchar(200) declare @holdCriteria varchar(200) declare @insertSQL varchar(5000) declare @ConvertRoutine varchar(500) declare @loopCtrl1 bit declare @loopCtrl2 bit declare @ConvertSQL varchar(5000) declare @PrevValue varchar(50) declare @NewValue varchar(50) declare @ActionTxt varchar(1) declare @Description varchar(20) declare @effDate varchar(10) declare @transEffDate varchar(10) declare @expDate varchar(10) declare @lastTransDate varchar(10) declare @policyStatus varchar(2) declare @reasAmendDesc varchar(50) declare @policyNumber varchar(20) declare @riskState varchar(20) declare @PriorPrem money declare @AmendPrem money declare @PremDiff money declare mtcursor cursor for select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1 where Column1 = @Var2 order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName open mtcursor fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' set @loopCtrl1 = 0 set @loopCtrl2 = 0 WHILE (@loopCtrl1 = 0) begin set @holdTable = @TableName set @holdCriteria = @extraCriteria set @holdTitle = @title if @FieldSelect = '' set @selectlist = @selectlist + ',' + @FieldName else set @selectlist = @selectlist + ',' + @FieldSelect set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)' fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine if @@fetch_status <> 0 set @loopCtrl2 = 1 if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1) begin set @tableBuild = @tableBuild + ')' set @insertSQL = ' declare mtcursor2 cursor for select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1 where TableName = ''' + @holdTable + ''' and ExtraCriteriaTxt = ''' + @holdCriteria + ''' and PageTitleTxt = ''' + @holdTitle + ''' and Column1 = ''' + @Var2 + ''' order by FieldDisplaySequenceNbr declare @FieldName varchar(50) declare @FieldTitle varchar(50) declare @ExtraUpdateMatch varchar(500) declare @PullUpdate bit declare @PullAdd bit declare @PullDelete bit declare @PullAnyUpdate bit open mtcursor2 fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate WHILE (@@fetch_status = 0) begin if substring(@FieldTitle,1,1) = ''#'' set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1) else set @FieldTitle = '''''''' + @FieldTitle + '''''''' if @PullAnyUpdate = 1 begin exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''') end else begin if @PullUpdate = 1 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '') end if @PullAdd = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''') if @PullDelete = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' from #tempTable A where Val1 = ''''D'''''') fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate end close mtcursor2 deallocate mtcursor2' exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL) set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' end if @loopCtrl2 = 1 set @loopCtrl1 = 1 end close mtcursor deallocate mtcursor Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 declare deletecursor cursor for select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U' open deletecursor fetch next from deletecursor into @PageName while @@fetch_status = 0 begin if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' fetch next from deletecursor into @PageName end close deletecursor deallocate deletecursor declare convertcursor cursor for select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a inner join MyTable1 b on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> '' where a.Id = @Var1 open convertcursor fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine while @@fetch_status = 0 begin set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)' set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) ' -- start 33385 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' --end 33385 if @ActionTxt = 'A' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + '''' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' end if @ActionTxt = 'D' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + '''' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' end set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted where EntrySequenceNbr = ''' + @Sequence + '''' exec (@ConvertSQL) fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine end close convertcursor deallocate convertcursor /* LOB-specific data conversions */ if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 -- End issue 33385 Create table #pageSeqTable (PageTitle varchar(50), PageSeq int) insert into #pageSeqTable select distinct PageTitleTxt, PageDisplaySequenceNbr from MyTable1 where Column1 = @Var2 select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt from MyTable2, #pageSeqTable b where Id = @Var1 and PageNm = b.PageTitle order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101), @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes, @policyNumber = PolicyNumber, @riskState = StateName, @AmendPrem = convert(money,PremiumAmount) /* Case 33385 */ from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C where Id = @Var1 AND Val2 = (select max(Val2) from SHPlanInfo where Id = @Var1) AND B.ReasonAmendedCd = A.ReasonAmendedCd AND C.StateCode = A.RiskState Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' Set @PremDiff = @AmendPrem - @PriorPrem select EffectiveDate = @effDate select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate select AmendXPolStat = @policyStatus select ReasonAmendedDes = @reasAmendDesc select PolicyNumber = @policyNumber select RiskState = @riskState select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiff Select ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
View Replies !
Stored Proc Calls Another Stored Proc
Hi all, I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this? Thanks for your help! Cat
View Replies !
Calling Stored Proc B From Stored Proc A
Hi all I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc The SELECT statement in question retrieves a single row from a table containing 10 columns. Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc? I know about stored proc return values and about output parameters, but I think I am looking for something different. Thanks
View Replies !
Calling A Stored Proc From Within Another Stored Proc
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help. I guess I should state my question to the forum ! Is there a way to call a stored proc from within another stored proc? Thanks In Advance. Tony
View Replies !
Calling T SQL Stored Proc From CLR Stored Proc
I would like to know if the following is possible/permissible: myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with INSERT INTO #tmpCLR EXECUTE spSQL } spSQL ( INSERT INTO #tmpABC EXECUTE spSQL2 ) spSQL2 ( // some other t-sql stored proc ) Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
View Replies !
Help With SQL Stored Proc
I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE) )BEGIN SET @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT @attendanceID = SCOPE_IDENTITY()ENDGO Kindly help with this
View Replies !
Need Help With A Stored Proc
I have a current stored proc that creates records based on certain criteria. One of the fields I have is a SmallDateTime field. To populate this from my stored proc, I have this code (for this one field). SELECT @myLeaveDate = CAST(STR(MONTH(getdate()))+'/'+STR(01)+'/'+STR(YEAR(getdate())) AS DateTime) This always creates a record for the 1st of the current month. This works fine as is. After it runs I can look at the table and it creates dates that look like the following: “2/1/2006� -Notice it doesn’t have any minutes, seconds, etc. Now what I need is to do something similar in another field which is also SmallDateTime, BUT I want the date to be for the 10th day of the following month. I got this working using dateadd, but it also appends the minutes, seconds, etc.
View Replies !
Sql And Stored Proc
Im trying to perform an update with a stored procedure thats all working but an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key I understand whats going on but how do you update some ones details if its protected this way. ?
View Replies !
SQL Store Proc - Need Help
I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd I also tried SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.
View Replies !
Proc Problem
Hi I have this stored_proc - which works fine ALTER PROCEDURE proc_Prod_AdvancedSearch @Title VARCHAR(50) = Null, @Category VARCHAR(8) = null, @ReleaseClass VARCHAR(50) = null, @BuyPrice DECIMAL(8,2) = nullASSET NOCOUNT ONSELECT *FROM ProductWHERE Title = ISNULL(@title, title)AND Category = ISNULL(@Category, Category)AND ReleaseClass = ISNULL(@ReleaseClass, ReleaseClass)AND BuyPrice < ISNULL(@BuyPrice, BuyPrice)Return except, when i type in something for the @title parameter, unless i type the exact match as title, it returns nothing i would like it to return results if part of @title is similar to title ive tried WHERE Title = ISNULL(' %' + @title + '%' , title) but it does the same thing as above any ideas cheers
View Replies !
Touchy Proc
I have been looking at this that past 20 minutes and it seems fine to me. I am trying to create it on my host account which has web based SQL manager(blows) so I can't really see whats truly wrong with it. But it gives me the error below: MessageDate will be the date passed in and CreateDate is a field with DateTime type. All the fields are there. Error -2147217900 Incorrect syntax near the keyword 'SELECT'. CREATE PROCEDURE prcGetMessages @MessageDate DateTimeSELECT tm.Title, tm.CreateDate, tu.TeamNameFROM tblMessage tmJOIN tblUserInfo tu ON tu.peID = tm.peIDWHERE tm.CreateDate > @MessageDate ---------------------------------------------------------------------------------CREATE PROCEDURE prcGetMessages @MessageDate DateTime SELECT tm.Title, tm.CreateDate, tu.TeamNameFROM tblMessage tmJOIN tblUserInfo tu ON tu.peID = tm.peIDWHERE tm.CreateDate > @MessageDateORDER BY tm.CreateDate ASC
View Replies !
Need Help With Stored Proc
I have 2 tables, lets say: Table1 and Table2. Table1 has an ID field that is unique (PK). Table2 has the same ID field and a date field which are both unique (PK, FK) I need to write a stored procedure that will query the ID field from table1 like Select distinct(id) from Table1 Would return: ID 1 2 3 4 5 Now on the first of every month I want a job to run that would create a new record for EACH ID from the above query. The new record would be created in Table2 with the ID and a date (that I will determine), resulting in: ID DATE 1 01/01/06 2 01/01/06 3 01/01/06 4 01/01/06 5 01/01/06 The following month, after running it again, the table would look like: ID DATE 1 01/01/06 1 02/01/06 2 01/01/06 2 02/01/06 3 01/01/06 3 02/01/06 4 01/01/06 4 02/01/06 5 01/01/06 5 02/01/06 I have no idea how to “loop� through this record set for each record in Table1 and in turn insert into Table2.
View Replies !
Stor Proc 101 Please
How I can get the best tutorial of using SQL Server 2005 Stored Procedured? For example this stor proc, -------------------------------------------------------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[COMPANY_ADD] -- Add the parameters for the stored procedure here @CompanyID INT, @CompanyName NVARCHAR(100), @CreatedDT Datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; insert into COMPANY(CompanyID, CompanyName, CreatedDT, LastModifiedDT) values (@CompanyID, @CompanyParentID, @CompanyContactID, @CompanyName, @CreatedDT, @CreatedDT) return @CompanyID END -------------------------------------------------------------------------------------------------------------- I want to get tutorial of the meaning line by line? Thanks
View Replies !
Need Help With A Store Proc
For a reason that I dont see my store proc is always returning 0 records but if I use a commandType.text instead of StoredProcedure then I get my results. So I must have some kind of error somewhere. Store Proc ALTER PROCEDURE dbo.Get_Cl_IssuesBySystemID @SystemId nvarchar(255) AS SET NOCOUNT ON SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue FROM dbo.IssueTracker_Issues t1 LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2 ON t1.IssueId = t2.IssueId Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%@SystemId%' order by t1.DateCreated SET NOCOUNT OFF Function Public Function Get_Cl_IssuesBySystemID(ByVal strSystemId As String) As DataView Dim objDs As New DataSet Dim objDv As New DataView Dim connect As New SqlConnection(strConnection) '***For CommanType.Text*** Dim strSelect As String strSelect = "SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue" & _ " FROM dbo.IssueTracker_Issues t1" & _ " LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2" & _ " ON t1.IssueId = t2.IssueId" & _ " Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%" & strSystemId & "%'" & _ " order by t1.DateCreated" Dim cmdSelect As New SqlCommand(strSelect, connect) cmdSelect.CommandType = CommandType.Text '***End For CommanType.Text*** '***For CommanType.StoredProcedure*** 'Dim cmdSelect As New SqlCommand("Get_Cl_IssuesBySystemID", connect) 'cmdSelect.CommandType = CommandType.StoredProcedure 'PARAM 'cmdSelect.Parameters.Add("@SystemId", strSystemId) '***End For CommanType.StoredProcedure*** Dim objAdapter As New SqlDataAdapter(cmdSelect) Try objAdapter.Fill(objDs) objDv = objDs.Tables(0).DefaultView Catch objErr As Exception Throw New Exception(objErr.Message) End Try Return objDv End Function
View Replies !
Store Proc - Need Some Help Please
I am doing an insert and I get the error "Cast from type 'DBNull' to type 'Integer' is not valid." if the contact name already exist. I dont see my error so if someone could enlight me I would appreciate. Thanks "****************STORE PROC*********************** ALTER procedure dbo.Add_Cl_Contact ( @ContactNamenvarchar(75), @Departmentnvarchar (50)=null, @Titlenvarchar(50)=null, @Phone1char(20)=null, @Phone2char(20)=null, @Phone3char(20)=null, @Ext1char(10)=null, @Ext2char(10)=null, @Ext3char(10)=null, @Faxnvarchar(50)=null, @Emailnvarchar(50)=null, @ContactTypeIDint=null, @Resultinteger OUTPUT AS if Exists ( Select * From Cl_Contacts Where ContactName = @ContactName ) Return 1 Else insert into Cl_Contacts ( ContactName, Department, Title, Phone1, Phone2, Phone3, Ext1, Ext2, Ext3, Fax, ContactTypeID, Email ) values ( @ContactName, @Department, @Title, @Phone1, @Phone2, @Phone3, @Ext1, @Ext2, @Ext3, @Fax, @ContactTypeID, @Email ) Select @Result = SCOPE_IDENTITY() return 0 "****************END STORE PROC*********************** '****************************************************************** '****************************************************************** Public Function Add_Cl_Contact(ByVal strContactName As String, _ ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _ ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _ ByVal strPhone3 As String, ByVal strTitle As String, ByVal iContactTypeId As Integer, _ ByRef iResult As Integer, ByRef strError As String) As Boolean '****************************************************************** Dim bSuccess As Boolean = True Dim connect As New SqlConnection(strConnection) Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect) Dim paramReturnValue As SqlParameter cmdSelect.CommandType = CommandType.StoredProcedure 'PARAM cmdSelect.Parameters.Add("@ContactName", strContactName) cmdSelect.Parameters.Add("@Department", strDep) cmdSelect.Parameters.Add("@Title", strTitle) cmdSelect.Parameters.Add("@Phone1", strPhone1) cmdSelect.Parameters.Add("@Phone2", strPhone2) cmdSelect.Parameters.Add("@Phone3", strPhone3) cmdSelect.Parameters.Add("@Ext1", strExt1) cmdSelect.Parameters.Add("@Ext2", strExt2) cmdSelect.Parameters.Add("@Ext3", strExt3) cmdSelect.Parameters.Add("@Fax", strFax) cmdSelect.Parameters.Add("@Email", strEmail) cmdSelect.Parameters.Add("@ContactTypeID", iContactTypeId) paramReturnValue = cmdSelect.Parameters.Add("@Result", SqlDbType.Int) paramReturnValue.Direction = ParameterDirection.Output paramReturnValue = cmdSelect.Parameters.Add("ReturnValue", SqlDbType.Int) paramReturnValue.Direction = ParameterDirection.ReturnValue Try connect.Open() cmdSelect.ExecuteNonQuery() iResult = cmdSelect.Parameters("@Result").Value connect.Close() If cmdSelect.Parameters("ReturnValue").Value = 0 Then strError = "Contact has been added" Else strError = strContactName & " already exist!" bSuccess = False End If Catch ex As Exception bSuccess = False strError = ex.Message Finally If connect.State = ConnectionState.Open Then connect.Close() End If End Try Return bSuccess End Function
View Replies !
My First Stored Proc
Hello, So I created my first SP today which returned data to populate a datagrid. Worked to perfection. Now I'm trying to do a simple login and I'm having a hard time getting it together. I want the user to enter in a username and pass then if user exists, to return their userid and update their last login date. But I can't get it. Any help would be awesome. Here's my SP... CREATE PROCEDURE [dbo].[userLogin] @username varchar(50), @pass varchar(50) AS declare @x int declare @userid int if exists (SELECT userid FROM users WHERE username = @username AND password = @pass) set @x = 1 else set @x = 0 if @x = 1 UPDATE users SET lastlogin = getdate() WHERE userid = (SELECT userid AS name FROM users WHERE username = @username AND password = @pass) else return '0' if @x = 1 SET @userid = (SELECT userid FROM users WHERE username = @username AND password = @pass) return @userid GO Any constructive criticism on my SP is welcome. Also, should I be using Print or Return if I want to send back a value to my VB code? And my VB: Dim user As String = txtUsername.Text Dim pass As String = txtPassword.Text Dim objDataSet As DataSet Dim objAdapter As SqlDataAdapter Try Dim cmd As New SqlCommand 'Enter Param's here Dim myParam1 As SqlParameter = cmd.Parameters.Add("@username", SqlDbType.VarChar) Dim myParam2 As SqlParameter = cmd.Parameters.Add("@pass", SqlDbType.VarChar) myParam1.Value = user myParam2.Value = pass objAdapter = New SqlDataAdapter objAdapter.SelectCommand = cmd objAdapter.SelectCommand.Connection = SqlConn objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure objAdapter.SelectCommand.CommandText = "userLogin" SqlConn.Open() Dim str As String = cmd.Parameters("@userid").Value() cmd.ExecuteNonQuery() If str = "0" Then 'Error Login Page Response.Redirect("login_err.aspx") Else Session("userid") = str Response.Redirect("home.aspx") End If SqlConn.Close() Catch ex As SqlException SqlConn.Close() End Try
View Replies !
Strore Proc With Like %
This must be a simple one but look all over to find infos on the uses of like % in a store proc and I did'nt find anything. I know it's something wrong with my "%" and where to put the apostrophes. Please could you check the following code and let me know what wrong with it. Thanks ALTER PROCEDURE dbo.GetSearchMultiColumn ( @strColumnNamenvarchar (50), @strSearchTermnvarchar (200) ) as if (@strColumnName = 'Camera') begin SELECT SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4 FROM Customers WHERE Camera1 LIKE '% @strSearchTerm %' or Camera2 LIKE '% @strSearchTerm %' or Camera3 LIKE '% @strSearchTerm %' or Camera4 LIKE '% @strSearchTerm %' return 0 end if (@strColumnName = 'ZMotor') begin SELECT SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3 FROM Customers WHERE ZMotor1 LIKE '% @strSearchTerm %' or ZMotor2 LIKE '% @strSearchTerm %' or ZMotor3 LIKE '% @strSearchTerm %' return 0 end if (@strColumnName = 'Stage') begin SELECT SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3 FROM Customers WHERE Stage1 LIKE '% @strSearchTerm %' or Stage2 LIKE '% @strSearchTerm %' or Stage3 LIKE '% @strSearchTerm %' return 0 end
View Replies !
SQL Stored Proc
Hi, I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT, I want to create a Geneirc search Proc that can handle a few criteria I was wondering if it is possible to create a VB like Select case depending on Information supplied to the stored proc i.e @Loc_Thing @Loc_OtherThing SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE Select case @Loc_Thing Case "Mickey" LastName = @Loc_OtherThing Case "Walt" Company = @Loc_OtherThing ...... etc is it possible to create a strored proc like this? I have found a Select case in SQL, but it doesn't work I would like it? Any Idea's?
View Replies !
Adhoc Vs PROC
Hi Guru,When I ran my adhoc script below it generated only 45000 reads or 4seconds but when I wrapped it into procedure it took about two minutesor millions of reads. The parameters calling both adhoc and proc areindeed the same. I'm pretty 99.9% sure that the proc does not recompilebecause I don't mix up between DDL and DML, no temp tables or any thingto cause proc to recompile. The big difference is adhoc used index scanfor 45% but proc used bookmark lookup for 75%. Why it's so differencesince they both returned the same results?Please help...Silaphet,Below is my code,DECLARE @Modevarchar(10),@UserIDvarchar(36),@FromDatesmalldatetime,@ToDatesmalldatetime,@Insttinyint,@LocationIDsmallint,@BunitIDtinyint,@TeamIDintSET @Mode='TEAM'SET @UserID=''SET @FromDate='Dec 1 2006 12:00AM'SET @ToDate='Dec 31 2006 12:00AM'SET @Inst=28SET @LocationID=0SET @BunitID=2SET @TeamID=805--IF @Mode = 'TEAM'BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_Grouped RIGHT OUTER JOINdbo.MyTeamsRpt ONvw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerIdLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (MyTeamID = @TeamID) AND ((StartDateBETWEEN @FromDate-365 AND @ToDate-365) OR (StartDate BETWEEN @FromDateAND @ToDate))GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveENDIF @Mode = 'RM'BEGINIF @BUnitId 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, OffBUnitDesc As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate)) AND OffBUnitID = @BUnitIDGROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveEND--ELSEIF @BUnitId = 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate))GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveENDENDEND
View Replies !
Stored Proc Or Something Else
Hi!I am trying to find stored procedure or a utility that will be able todelete files older than let's say 3 days but I would want to pass a dayparameter, file dir and maybe an extension or something like that.Anybody knows of a tool or a stored procedure that does that?Thank you,T.
View Replies !
Using A Stored Proc Within A Stored Proc
I want to determin if a user belongs to a certain group or not within a stored proc: CREATE PROCEDURE usp_General_IsEstimator @cLogin sysname, @bEstimator bit AS DECLARE groups CURSOR FOR EXEC sp_helpuser @cLogin What I was planning on doing is to see if the user login returns the 'Estimating' group name using the sp_helpuser. If so, set @bEstimator = 1 else 0 How can I accomplish this? I cannot use the cursor with the stored proc. How do I work with the recordset returned by the stored proc? Any ideas? Mike B
View Replies !
Execute MDX From SQL Proc
-------------------------------------------------------------------------------- Can someone shed some light??? I have searched hi and low for an answer to my dilemma. I have an mdx query that works fine in my olap environment but when I try to run the same query from sql 2000, one of my calculations comes back as a long int and includes (E-02) at the end. Can you please review my store procedure and provide some insight? SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER PROCEDURE GetWeeklyTrendData @Level int, @year int, @week int, @PrintMDX int = 0 AS SET NOCOUNT ON DECLARE @MDX varchar(4000), @CubeQuery varchar(4500), @Leaf int, @Tier int BEGIN SET @MDX = ''' WITH MEMBER [Measures].[OrgName] As ''''[GL].CurrentMember.Name'''' MEMBER [Measures].[LevelSKey] As ''''[GL].CurrentMember.Properties("Key")'''' MEMBER [Measures].[OrgType] As ''''Iif(IsLeaf([GL].CurrentMember), 1, 0)'''' MEMBER Measures.Lookp AS '''' LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )'''' MEMBER Measures.Trend AS '''' Measures.Customers / LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )'''' , SOLVE_ORDER = 1, FORMAT_STRING = ''''Percent'''' SELECT { CrossJoin ( { [Week].[' + CAST(@year AS VARCHAR) + '].[Week ' + CAST(@week AS VARCHAR)+ '].Lag(12) : [Week].[' + CAST(@year AS VARCHAR) + '].[Week ' + CAST(@week AS VARCHAR) + '] }, { Measures.OrgName, Measures.LevelSKey, Measures.OrgType, Measures.Customers, Measures.Lookp , Measures.Trend } ) } ON COLUMNS, NON EMPTY { [GL].&[' + CAST (@Level AS VARCHAR) + '].Children } ON Rows FROM [cubeName] WHERE ( [Monthly Income].[All Monthly Income].[Y],[Contact Events].[All Contact Events].[Y], [Other Financial].[All Other Financial].[Y],[Bankers Notes].[All Bankers Notes].[Y], [Employer Name].[All Employer Name].[Y] ) ''' END SELECT @CubeQuery = ' SELECT * FROM OPENQUERY(ReportDBMart, ' + @MDX + ')' IF @PrintMDX = 1 PRINT 'MDX: ' + @MDX EXEC(@CubeQuery) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO The following is the value that I get back. 9.4993517502742597E-2 and is derived from Measures.Trend. My guess is that it doesn’t recognize FORMAT_STRING = ''''Percent'''' or its trying to return a numeric value inside a varchar. Any thoughts???
View Replies !
Help Stored Proc
Hi, Could any one please help me in creating sp. It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100. Thanks in advance. -ss
View Replies !
Some Simple Proc Help
hey guys very new to sql server - i can take care of the basic CRUD but,i'm not familiar with some of the syntax to handle stuff right in the proc. i've been making multiple round trips to accomplish what i think i should be able to do based on a condition right when im in the procedure itself. so, please bear with me. i have a table with these 5 fields. "AltNumb" "AltName" "TimeLastRun" "TimeNextRun" "skdDuration" ALT001 FIRST ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:35:00 PM 5 ALT002 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:43:00 PM 10 ALT003 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:48:00 PM 15 i have a service that will call a procedure that takes the current_timestamp and compares it (datediff) with the column "TimeLastRun" and see's if it's equal with the "SkdDuration" - some are set to 5, 10, 15 minutess etc. heres's the short lil proc below: CREATE PROCEDURE dbo.Check_Time_Duration AS declare @myDate smalldatetime select @myDate = getdate() SELECT AlertNumber from time_check2 where DATEDIFF(MINUTE,timeLastRun,@myDate) = Schedule_Duration what i'd like to do is if anything matches, is Update the two columns "TimeLastRun" & "TimeNextRun" right on the spot based on the AlertNumber(if that row matches in the above query). something like this: DECLARE @myDate smalldatetime select @myDate = getDate() Update time_check2 set timelastrun=@myDate,timenextrun=dateadd(minute,5,@ myDate)where AlertNumber ='ALT001'--this would need to be a variable BUT the AlertNumber of course matched in the first select Hopefully someone can see what i'm trying to accomplish here and give me some help Been on Oracle too long - i feel like a real newbie here thanks again rik
View Replies !
Sp_addarticle, Proc Has Bug With No Know Fix
I have found a bug with the sp_addarticle procedure and have not had any success finding a fix for it. The bug only exists whenever I am adding an article that is vertically partitioned and uses a DTS package to do the processing. In this particular case, I am using snapshot repl. Has anyone experienced this issue before? Here is the statement... exec sp_addarticle @publication = N'PublicationName', @article = N'ArticleName', @source_owner = N'dbo', @source_object = N'ArticleName', @destination_table = N'ArticleName', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 16, @vertical_partition = N'true', @ins_cmd = N'CALL sp_MSins_ArticleName', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false' Here is the error that gets produced... Server: Msg 156, Level 15, State 1, Procedure syncobj_0x4344414345344530, Line 1 Incorrect syntax near the keyword 'from'. Any help would be greatly appreciated!
View Replies !
Stored Proc
i have a table of the form table tab1( col1 bigint (ForeignKey col1 references mastertable1(col1)) col2 bigint (ForeignKey col2 references mastertable2(col2)) ) when i delete col2 from mastertable2, all the records in tab1 with the col2 value must be deleted. my problem is how to iterate through all the matching records in tab1 when i delete col2 from master table 2, using a SP
View Replies !
Stored Proc
i know this might seem a bit stupid to ask - but how do i use IF statements in sql? i'm having difficulty incorporating statements like : if @date in (csfsc.bgndate1) to (csfsc.enddate1) then 1 else if @date in (csfsc.bgndate10)to (csfsc.enddate10) then 10 else if @date in (csfsc.bgndate11) to (csfsc.enddate11) then 11 else if @date in (csfsc.bgndate12) to (csfsc.enddate12) then 12 else into my query...where should this go? PLZ HELP!!!! kind regards
View Replies !
Stored Proc
Hi, I am running SQL 6.5 with sp4. We are using a stored proc to convert the signals from hub to sql server and this stored proc is executes 100-150 times per minutes. Now problem is some time hub executing this stored proc block the other process and to getrid of this we have to recompile the stored proc. After recompiling the stored porc everything is fine, but here we cann't do recompileing as we have to stop hub and it results in lost of signals. Can anybody help me why this is happening and how to solve this problem. Thanx in advance, Mark
View Replies !
USE DbName In Proc...
Need to have 'Use Master' statement in one stored procedure from a Admin database to execute some task only in Master database could be performed. error: Use database statement is not allowed in a procedure or trigger. Is there a way to bypass this error? I know my last result will be to put this stored procedure in Master, which I really don't want to... thanks for help -D
View Replies !
Extended Proc?
Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'. found above in error.log almost all the SQL 2k servers. What is it doing exactly for? I could run sp_MSgetversion without that xpstar.dll? thanks David
View Replies !
T-sql Stored Proc
I want to generate getdate without the timestamp, only the date. Any ideas. Datepart will not convert to varchar as needed within this proc.
View Replies !
Stored Proc Std. Doc?
Hi, Anybody has Document for Stored procedure coding standard for best performance. I know that I can able get in BOL and sql-server-performance.com just wanted know if anybody had info other than this. Thanks, Ravi
View Replies !
Stored Proc / T-SQL
One of the columns I am inserting into may or may not contain sensitive data. If the "type" parameter supplied with the proc. execute statement is of a specific value I need to utilize an encryption function (and a data type conversion) on the following column which is the actual value parameter. If the type does not meet the specific criteria the encryption/conversion for the value parameter. Any syntax assist would be greatly apprciated.
View Replies !
|
TRACKER
