FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query. Can Someone tell me where is the problem? Thanks
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission. How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.
I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0 ,@OriginalMax DECIMAL=0 AS DECLARE @CRI8 VARCHAR(500)SELECT @CRI1='' SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax' SELECT @Criteria = ......+ @CRI8 When I execute this stored procedure, I get the following error message. SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin'. Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
I have a Stored Procedure as followsUSE [MyDataBase] GO /****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SPLogins] ( @LoginName varchar(50), @LoginD int output, ) AS Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value What Is the correct wayt to go about it.
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck 2 @PID INTEGER = -1 OUTPUT, 3 @PCityFrom Char(3) = '', 4 @PCityTo Char(3) = '', 5 @PDateFrom DATETIME = '31 Dec 9999', 6 @PDateTo DATETIME = '31 Dec 9999', 7 @PIsExists BIT = 1 OUTPUT 8 AS 9 10 CREATE TABLE #TmpControlRequst 11 ( 12 IDINTEGER, 13 IsExistsCHAR(1) 14 ) 15 /*###Pseudo 16 1. Check the Date From and Date To 17 -- select all the value equal to parameter cityFrom and cityTo 18 -- insert the selection records into tmp table 19 --*/ 20 INSERT INTO #TmpControlRequst 21 (ID, IsExists) 22 SELECT ID, 23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 35 ELSE 0 END 36 FROM RequestTable 37 WHERE ID <> @PID 38 AND CityFrom = @PCityFrom 39 AND CityTo = @PCityTo 40 41 --======== FINAL RESULT 42 -- For tmp table:- 43 -- isExists = 1 ==> date lapse 44 -- isExists = 0 ==> date ok 45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting 46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1 47 ELSE 0 END 48 FROM #TmpControlRequst 49 WHEREIsExists = 1 50 51 SELECT @PIsExists 52 --========= 53 54 DROP TABLE #TmpControlRequst 55 56 --========= 57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
Hi, guys I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table. could you correct my code, if you know what is the problem?
thanks
My contract table DDL: ************************************************** ***
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
My insert stored procedure is: ************************************************** *****
create proc sp_insert_new_contract ( @contractDate[smalldatetime], @tuition [money], @studentId[char](4), @contactId[int]) as
if not exists (select studentid from student where studentid = @studentId) begin print 'studentid is not a valid id' return -1 end
if not exists (select contactId from contact where contactId = @contactId) begin print 'contactid is not a valid id' return -1 end begin transaction
/*Error Check */ if @@error !=0 or @@rowcount !=1 begin rollback transaction print ‘Insert is failed’ return -1 end print ’New contract has been added’
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Not sure this is the right forum as I'm not sure quite what the problem is, but I have a feeeling it's the stored procedure that I'm using to replace the SQL string I used previously.I have a search form used to find records in a (SQL Server 2005) db. The form has a number of textboxes and corresponding checkboxes. The user types in a value they want to search for (e.g. search for a surname) and then selects the corresponding checkbox to indicate this. Or they can search for both surname and firstname by typing in the values in the correct textboxes and selecting the checkboxes corressponding to surname and firstname.The code to make this work looks like this:---------------------------------------- Dim conn As SqlConnection Dim comm As SqlCommand Dim param As SqlParameter Dim param2 As SqlParameter Dim param3 As SqlParameter Dim param4 As SqlParameter Dim objDataset As DataSet Dim objAdapter As SqlDataAdapter conn=NewSqlConnection("blah, blah") comm = New SqlCommand 'set properties of comm so it uses conn & recognises which stored proc to execute comm.Connection = conn comm.CommandText = "SPSearchTest3" comm.CommandType = CommandType.StoredProcedure 'create input parameter, set it's type and value param = comm.CreateParameter param.ParameterName = "@empid" param.Direction = ParameterDirection.Input param.Value = txtPatID.Text param2 = comm.CreateParameter param2.ParameterName = "@LastName" param2.Direction = ParameterDirection.Input param2.Value = txtSurname.Text comm.Parameters.Add(param) comm.Parameters.Add(param2) conn.Open() objAdapter = New SqlDataAdapter(comm) objDataset = New DataSet objAdapter.Fill(objDataset) dgrdRegistration.DataSource = objDataset dgrdRegistration.DataBind() conn.Close()------------------------------------While the stored procedure is this:------------------------------ @EmpID int, @LastName nvarchar(20) ASSELECT EmployeeID, LastName, Firstname, BirthDate, Address, title, addressFROM employeesWHERE (DataLength(@EmpID) = 0 OR EmployeeID = @EmpID)AND (DataLength(@LastName) = 0 OR LastName = @LastName)------------------------------This will work if I search using EmployeeID and Surname or only by EmployeeID, but I don't get any results if I search only for Surname, even though I know the record(s) exits in the db and I've spelled it correctly. Can someone point out where I'm going wrong?(Incidentally if I have a procedure with has only one parameter 'surname' or 'employeeID', it works fine!)Thanks very much and sorry about the long-winded post.
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
HiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement. ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT )
ASSELECT * FROM ( SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
This procedure gives a error : " Msg 245, Level 16, State 1, Procedure YAMAN, Line 16 Conversion failed when converting the nvarchar value 'user' to data type int. " How can i return string value
ALTER procedure [dbo].[YAMAN] (@username varchar(20),@active varchar(20)) as begin if exists (select username from aspnet_Users where username=@username) begin if @active=(select active from aspnet_Users where username=@username) return 'already exist' else begin update aspnet_Users set active=@active where username=@username return 'update' end end else return 'user does not exist' end
I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.
I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.
Here is what I know needs to be done but not sure how to put into the procedure.
I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this
Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq
I'm passing a comma delimited string to my SP, e.g.:"3,8,10,16,23,24"I need to retreive each number in this string and for every number found I need to execute some sode, say add "AND SportID="+numberfoundHow can I do that?
Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work. I'm baffled since I know how to pass an integer to a stored procedure. In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db. If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure. Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below. Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception ProcessModuleLoadException(Me, exc)End TryEnd Sub---------------------------------------- *******************Controller.vb file******************* Public Function SearchBlog(ByVal strSearch As String) As ArrayList Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo)) End Function---------------------------------------- ******************* DataProvider.vb file******************* Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader---------------------------------------- *******************SqlDataProvider.vb file******************* Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function---------------------------------------- *******************Stored Procedure******************* CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------
Hello, I have a question on sql stored procedures.I have such a procedure, which returnes me rows with ID-s.Then in my asp.net page I make from that Id-s a string likeSELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18. My question is: Can I do the same in my stored procedure? Here is it:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById] ( @Id int ) AS declare @path varchar(100); SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id; SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories] WHERE Path LIKE @Path + '%' ORDER BY Path Thank youArtashes
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Development - Visual Studio 2005 Database - MS SQL 2005
I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.
This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.
So... Does anyone here can help me out? Thanks in advance.
I have a multivalued parameters populated from a dataset with country codes: US,CA,HK etc... I tried to pass the parameter to the stored procedured but that did not work. I did some research and found out that I won't be able to do that.
Writing Queries that Map to Multivalued Report Parameters
You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied: The data source must be SQL Server, Oracle, or Analysis Services. The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure. The query must use an IN statement to specify the parameter. If I am not able to pass the parameter that way, what is the best way to accomplish my goal? Thanks, Elie