My situation is this: I have a requirement table,in the requirement table i have a field which is allow_multiple,if it allows multiple the value is 1 and 0 if it will not allow and also I have an insert storedprocedure for inserting licenses which will be added in the Staffl table.When i insert a new license for the staff,it should check in the requirement table if the new inserted lincense does have 0 or 1 value in the allow multiple field.If it has a value of 0 the new license should not be inserted.What would be the good way to compare the existing licenses of the staff and the newluy added?
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though. So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>" Is there an way to accomplish this inside a stored procedure?
Hi all I have a table with one nText Field , some times i need to realize whether two records have the same value in that nText Field or not , so how can i compare these two records based on their ntext Fields.? in the other words i'm looking for some thing like the follows: declare @a nTextdeclare @b nText select @a=MynTextField from MyTable where PK=18select @b=MynTextField from MyTable where PK=21 if @a=@b print 'Records Are the same'else print 'Record are not the same'
I need to build a cube for membership over time. Active membership is the gap from the record's CreationDate to SuspendedDate, as compared to a reference date (today's active members have CreationDates before today, and SuspendedDates > today, or NULL).
Any idea how to build a cube so I can count membership over a Time dimension? The only approach I've been able to come up with is to create a table with a record for each active member on each date (brute force).
I am working with the article that MAK wrote on SecurityLogs http://www.databasejournal.com/features/mssql/article.php/3515886
I have completed this, but I have made some changes to the database (for normalization to 3NF purposes). I now have problems with a query.
I am trying to "Insert a new record in a table if it does not already exist in the table". To try to clarify I perform the following query:
INSERT INTO Tmp_Event SELECT DISTINCT EventID, EventType, EventTypeName from Tmp
Which gives me the Tmp_Event table consisting of EventID's etc. (no duplicates). What I then want to do, is compare the 'Tmp_event' table and an already existing 'Event' table. These two tables are in fact identical. I would like to insert any records from 'Tmp_Event' into 'Event' if they do not already exist in 'Event'.
This query gives me all records that do not exist in 'Event'
SELECT EventID, EventType, EventTYpeName from Tmp_Event WHERE EventID NOT IN (SELECT EventID from Event)
How can I change this query into performing an INSERT INTO Event as well?
I have a table with a DateTime field that has Dates in the format of mm/dd/yyyy I want to do a simple count on the table for the previous day but keep getting 0 for the results. Here is the script I've been trying.
SELECT COUNT(*) FROM mytbl WHERE Collected_Date = DateAdd (dd, -1, GetDate())
It appears to me that the time portion of the DateAdd function keeps the matches that I am looking for from happening. What am I missing or am I going about this all wrong?
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example NAME1 = JAY JOHN SMITH NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
I have the following table structure.group1 group2 group1_result group2_result'One' 'Two' 3 2'One' 'Two' 3 1'One' 'Two' 2 5'One' 'Two' 4 1'One' 'Two' 0 5I need to sum up the number of times 'One' is greater than 'Two', andvice-versa. For example, the result I would like to achieve is asfollows.group1 group2 group1_total group2_total'One' 'Two' 3 2I'm using the following SQL statement, but I get 5 rows returned,giving me a '1' or '0' for each row.select group1, group2,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END),sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)FROM table1GROUP BY group1, group2Any help would be greatly appreciated.
I have a old report that was pointing to a different database, and i changed the connection string for that report and changed the field names (As per my new stored Procedure). In some places I keep getting this run time error, i have tried looking for all the hidden references to this field and i cannot find any parameter that the old database had. The error messages that i get are as follows:
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_Admin_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_FA_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblWithdrawals€™ refers to the field €˜PW_Comment€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Can Someone please help me out as to how should i get rid of these error messages, they are kinda driving me nuts.
I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...
I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(
How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Hi all, I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me. Thank You
Hi I use the next StoredProcedure in Access, Inserts Users to tblUsers: INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments ) VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:Luca de Angelis 7 -- Create date: 22/08/2007 8 -- Description:Inserimento dei dati contabili nella tabella di log 9 -- ============================================= 10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili 11 -- Add the parameters for the stored procedure here 12 @id_gestore tinyint 13 AS 14 SET NOCOUNT ON 15 BEGIN TRANSACTION 16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 17 SELECT CEL_traffico_temp.numero_telefonico 18 , CEL_traffico_temp.anno 19 , CEL_traffico_temp.mese 20 , @id_gestore as id_gestore 21 , 1 22 FROM CEL_traffico_temp 23 INNER JOIN 24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella 25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 26 27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 28 SELECT CEL_traffico_temp.numero_telefonico 29 , CEL_traffico_temp.anno 30 , CEL_traffico_temp.mese 31 , @id_gestore as id_gestore 32 , 2 33 FROM CEL_traffico_temp 34 INNER JOIN 35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella 36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 37 38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 39 SELECT CEL_traffico_temp.numero_telefonico 40 , CEL_traffico_temp.anno 41 , CEL_traffico_temp.mese 42 , @id_gestore as id_gestore 43 , 3 44 FROM CEL_traffico_temp 45 INNER JOIN 46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella 47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 48 49 IF @@error <> 0 50 BEGIN 51 ROLLBACK TRANSACTION 52 END 53 ELSE 54 BEGIN 55 COMMIT TRANSACTION 56 END Help me please...
Visual Studio 2008 Code VB I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table. Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
AS BEGIN IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID, Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID, @Status,@CaseType,@Offense)
To all, I looked at the MS-SQL pubs sample database and execute the example stored procedure reptq2 and I got 17 results set back. Where can I find an example using Visual Studio DataGrid or any means to get all these results from this SP.
AS BEGIN IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number) BEGIN UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID END ELSE BEGIN print 'CANNOT UPDATE' END END
This my storedproc. My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this. Thankyou Ramya.
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets) Here is the function to fill the RBL: 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 cmd.CommandText = "usp_responses_sel" 5 cmd.CommandType = Data.CommandType.StoredProcedure 6 7 ' Fill usp_ with Parameters 8 cmd.Parameters.AddWithValue("QuestionID", QuestionID) 9 cmd.Parameters.AddWithValue("LocaleID", LocaleID) 10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum) 11 12 Dim da As New SqlDataAdapter 13 da.SelectCommand = cmd 14 Dim ds As New DataSet 15 da.Fill(ds, "response") 16 Return ds 17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist. Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2) 2 rblResponses2.DataSource = ds 3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound? Thanks
hi i have a stored procedure witch Returns 0 or 1 dependig if exists some rows how can i acces that value in code behind? i tryied all, command.ExecuteScalar(), command.ExecuteNonQuery() but none works i need something like if(command.GetReurnValue) .... else .... thanks in advance
Can I know the best method to connect the sql server 2008 with Visual studio 2008, it will be helpful if I know how to access the stored procedures in the sqlserver via LINQ.
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
Hello everyone,I am developing forums (Discussion Board) in C#.net 2005 with SqlServer. Right now i am having problem in fetching data from two tables.Here are the tables from which i want to fetch data.Topics Table Threads TableTopicID ThreadIDForumID TopicIDTopicName SubjectTopicDescription Replies UserID LastPostDatenow i want to fetch all the topic talbe data as well as total no of threads,Lastpostdate,UserID per topic.I am able to fetch topic table data and Total no of threads per topic through the following query.SELECT TopicID, ForumID, TopicName, TopicDescription,(SELECT COUNT(ThreadID) FROM Portal_Threads WHERE (TopicID = Portal_Topics.TopicID)) AS Threads FROM Portal_Topicsbut i am not able to fetch anoter two detail with subquery as i am getting error likeonly one expression can be specified in the select listorsubquery returned more than one value.can anyone tell me how can i fetch these two values per topic. should i use stored procedure and create temporary table and after fetching these values i can store it in temporary table and i can fetch values from that temporary table...please provide code snippet if possible as i've never used sqlserver before..Thanks in advance...Regards,Nil