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.
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.
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).
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
IF NOT EXISTS (Expression evaluates to true) BEGIN INSERT INTO Table (Account, CustomerName ) VALUES (@Account, @CustomerName)
SELECT Column FROM Table WHERE PrimaryKey = PrimaryKeyJustCreatedAbove END
I'm creating a new record in the table, after I create this record I want to select the columns from the newly created record. Problem is the only unique value is the primarykey that is created with the record. Is there a way to identify that value within the statement?
I want to test some times it takes for a proc to run. Since I work for a large company, I rarely have access to the SQL server in a capacity where I can use profiler and such. Are there any quick and easy ways to just surround blocks of code in a T-SQL statement to get an accurate reading on how long it takes?I.e., if I surround with GETDATE() before and after, does that also measure the round trips to the server, or just the execution time.I want to just compare some different methods and see what is quicker. THX.
Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the query I currently have for performing the insert. CREATE PROCEDURE [dbo].[VacancyGet] ( @CustomerName nvarchar(100), @ClientCode nvarchar(10) ) AS INSERT INTO Renter ( CustomerName, ClientCode, ) VALUES ( @CustomerName, @ClientCode, )
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
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.
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.
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
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
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 ***
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
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':
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?
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?
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.
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.
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
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'.
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. ?
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
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?
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
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)
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
'****************************************************************** '****************************************************************** 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
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
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
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
" 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***
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
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.
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.
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
