Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.
select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
What I would like is in effect a function Highest() Eg.
select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle.
Many thanks
Idea here is to output results of a stored proc to a text file..
I am trying to use this query to execute a stored proc which in turn accepts 3 parameters. Can some one help me as I am getting syntax errors when I try this way:
I know, it's a job posting site, but our company is urgently looking for an expert senior level sql server DBA who can be rated as 9.5 out of 10 in sql server dba activities who are hard to find! Top hourly pay on a long contract in East Coast! so anyone interested? please rush resume to MessageDBfan@yahoo.com
Hi, I need help with updating a record. When I run the following code underneath the update button, the record does not update and I get no error message. Anyone have any ideas?
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString Dim Description As String = DescriptionTB.Text Dim PlaceName As String = PlaceNameTB.Text Dim Location As String = LocationTB.SelectedValue Dim PropertyType As String = LocationTB.SelectedValue Dim Price As Integer = PriceTB.Text Dim con As New SqlConnection(ListingConnection)
Dim sql As String = "Update Listings SET Description = ?, PlaceName = ?, Location = ?, PropertyType = ?, Price = ? WHERE ListingID = ?" Dim cmd As New SqlCommand(Sql, con)
We found a SQL problem when searching in a large communty (GCN). Please, we need someone how can help with this problem. It's urgent.
For details see post: Page 1: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806 Page 2: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806&PageIndex=2
I have done a couple of little projects in VB express and was pleased with the results. Now I have something bigger in mind and need to know if VB express version of SQL will support merge replication. If not will the full version support it? Thanks Dave
Lets just say that I have really only logged into phpmyadmin once and messed up my forum.
What I was trying to do? Install a shoutbox (chatroom type software). When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.
I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.
If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.
Click on the link to see the error..
http://www.cflzone.com/forum/index.php
Can someone that knows what they are doing please help me???
Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass MyClass.Property1 = "Hello" MyClass.Property2 = Unit.Pixel(100) MyClass.Property3 = 100Or even a control:Dim MyLabel As Label MyLabel.Id = "MyLabel" MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.
This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.
But if I start the Query i get this failure message: Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.
It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.
For your information the whole Code:
Code:
declare @error varchar, @rowcount varchar
select count(*) from dbo.temp1 if (@@error <> 0) begin Print 'An error occurred reading the temporary import table' goto exitpoint end
select @error = @@error,@rowcount=@@rowcount if @error <> 0 begin print ' Error updating - Error number '+@error+'. Rolling back' --this reverses your updates rollback --this jumps to the end goto exitpoint end
print @rowcount+' rows updated'
insert into dbo.Orderposreg (Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring) select Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring from dbo.Temp1 where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg) select @error = @@error,@rowcount=@@rowcount if @error <> 0 begin print ' Error inserting - Error number '+@error print ' Rolling back updates and inserts' --this reverses your updates and inserts rollback --this jumps to the end goto exitpoint end
print @rowcount+' rows were inserted' --this saves your data commit
Hi ! I have been worked with VC++, MS SQL SERVER, Transact-SQL for 3 years. I made an axtended stored procedure (xp_test) which returns an recordset. From Query Analizer, I can see the recordest : exec xp_test
I want to make an User Defined Function - MyTest which return the recordset that it is returned by xp_test after its execution. Something like that :
CREATE function dbo.MyTest ( ) RETURNS @table ... AS BEGIN exec master.. xp_test table1 output -- can I do this ?
I do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
Hi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system. But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine. Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID. My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?) My Second question, what is the best way to design this kind of structure to get the highest performance benifit?
All kind of thoughts will be appreciated much! thanks
I have a table describing items with 3 rows: ID : its an identity Primary key Type : nvarchar string for itemtype ItemData : numeric data for an item
I would like to create a query to get the results where each returned row contains - Type - number of items for the given type - concatenated string of the ItemData numbers for all items for the given type
SELECT Type , COUNT(Type) AS Amount FROM Items GROUP BY Type
but i cannot figure out how to do the string concatenation (its like SUM but we dont need to add the values but we need to concatenate). I was thinking some stored procedure but this query will run on Compact SQL so thats not possible according to Compact SQL specs. Can any SQL expert help, how to do this, or is it possible to do anyhow?
First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is : --------------------------------------------- CREATE PROCEDURE Top1 @tblname Varchar(255) AS
EXEC ( 'SELECT TOP 1 * FROM ' + @tblname + ' WHERE Status="OLD" ORDER BY ID DESC') GO ----------------------------------------------- The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors : -------------------------------------------------------------------------------------------------- CREATE PROCEDURE AddNewStory @tblname varchar(50), @title varchar(50), @Content varchar(5000) , @Picture varchar(200) , @date datetime AS
Server: Msg 128, Level 15, State 1, Line 2 The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. Stored Procedure: db1sql.dbo.AddNewStory Return Code = 0
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
I have two tables - T_Assets and T_Inspections. I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL: SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection) FROM T_Assets INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this. Jagdip
Hi All Professionals Programmers, I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can. Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc. you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships. Hope you have understood what i am going to say and need advice of professional and expert user to resolve it. Any concise quality material like articles, white paper etc will also be suitable for me. Thanks in Advance
CREATE PROCEDURE SP_ProcessAdminResults(@UserID nvarchar(100)) As
Declare @ReqID as integer; Declare @Assaignee as integer; Declare @TechName as nvarchar(200); Declare @OpenDate as datetime; Declare @GroupID as integer; Declare @DateActiontaken as datetime; Declare @AssignedDate as datetime; Declare @ActionDays as integer; Declare @OpenDays as integer;
declare GroupCursor cursor local scroll static for select [ReqID],[Assignee],FullName,[OpenDate],[GroupID],[DateActionTaken] from V_ProcessRequestMaster where UserID=@UserID and [Status]<>'CL' order by ReqID open GroupCursor fetch first from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken while (@@fetch_status <>-1) begin select @AssignedDate= min(DateLogged) from LOGMASTER where analyst=@Assaignee and reqMasterID=@ReqID and UserID=@UserID and Type='INIT' If @AssignedDate is null Begin select @AssignedDate=dbo.GetActionDate(LTRIM(Rtrim(@TechN ame)),@ReqID,@UserID) End
If @AssignedDate is null BEGIN set @ActionDays=0 END Else BEGIN If @GroupID=438030 BEGIN set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,5 ,6) set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),5,6) END Else BEGIN set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,1 ,7) set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),1,7) END
END If @ActionDays<>0 set @ActionDays=@ActionDays-1 If @OpenDays<>0 set @OpenDays=@OpenDays-1
update requestmaster set DateAssigned=@AssignedDate, ActionDays=@ActionDays, OpenDays=@OpenDays where ReqID=@ReqID and UserID=@UserID fetch next from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken end GO
The master table contains like 600,000 odd records and when i put a cursor and process the results where i need to get the business days between assigned date and action taken date the query take lot of time and eventually the front end crashes saying Time OUT expired.
Looking for an expert solution to simplify that query.
Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are: 1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type? 2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?
This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?
i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK
All: This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Im trying to insert a new record into tblMessages...the unique ID generated for MessageID is the value I want to use as a value for the MessageID field when inserting a new record in tblUsersAndMessagesI have the following sp: -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE spNewMessage @UserIDSender nvarchar(256), @MessageTitle nvarchar(50), @MessageContent text, @MessageType int, @MessageID uniqueidentifier, @UserID uniqueidentifier AS Begin Set NoCount on DECLARE @WhateverID uniqueidentifier INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType) VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType) Select @WhateverID=@@Identity INSERT INTO tblUsersAndMessages(MessageID,UserID) VALUES (@WhateverID,@UserID) End GO And here are my tables:tblUsersAndMessages allow nullsMessageID uniqueidentifier falseUserID uniqueidentifier falseNew bit false *default set to ((1)) tblMessages allow nullsMessageID uniqueidentifier false *PK default set to (newid())UserIDSender uniqueidentifier falseMessageTitle nvarchar(50) trueMessageContent text trueSentDateTime datetime false * default set to (getdate())MessageType int false Now when I try to add my SP I get this error:Msg 206, Level 16, State 2, Procedure spNewMessage, Line 22Operand type clash: numeric is incompatible with uniqueidentifierWhat can this be?!?
This is such a complex question and I'm 99.9% sure it requires usage of Dijkstra's algorithm in order to determine the shortest path. :(I have tried to build this myself (yes, I've viewed enough examples on the web, but since they dont exactly do what I want AND I'm rather new to this advanced SQL AND my boss would really like this asap I feel forced to call upon the community)Basically I need a query which analyzes the relationships between 2 persons and returns the shortest path(S!) I have provided the data that is required to perform any tests below. The example I provide match with the given data.I know for sure that such a query has been written before since for example LinkedIN uses something similar...so if anyone has this off the shelf for me great!If not, I would really really appreciate it if someone could provide a completely worked out example. I'll even give special thanks to that person on our future website :)So, many thanks ahead for whoever takes up this challenge! :)CASE:-----------------------------------------------------------------------------I have tables with friend relationships and tables with userdata.Lets say im logged in as Peter (usercode 5).Now if I (as user Peter) view the profile of Andre (usercode 51), I want to determine the relationship that exists between me and Andre.When the users would have a direct relationship, eg. between Peter (5) and John (6) I want returned:col1 col2 col3 col4 5 Peter 6 JohnWhen the users would have a indirect relationship, witch EXACTLY 1 person in between, like between John (6) and Jack (48).So I can go from John to Jack in exactly 2 steps via multiple persons, in this case I want the following rows returned (max 4):col1 col2 col3 col4 col5 col6 6 John 11 Hans 48 Jack6 John 15 Hans 48 JackWhen the users would have a indirect relationship, witch MORE than 1 persons in between, like between Peter (5) and Andre (48), I want returned:col1 col2 col3 col4 col5 col6 col7 col85 Peter 11 Hans 48 Jack 51 AndreIn any case when there are multiple paths from person A to person B, I only want the shortest paths returned to a maximum of 4Since this query will be called may times by different users at the same time concurrency issues also need to be taken into account (e.g. usage of temp tables)with the entire query the maximum amount of steps that should be checked is 6, so maximum 6 persons in between 2 persons.So if a viewed user is more than 6 steps away from the viewing user I want no results returned.E.g. when Peter (5) views the profile of Simon (7), no relationship exists through any other person, and an empty dataset should be returned.-----------------------------------------------------------------------------I have the following tables and data:CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]CREATE TABLE [dbo].[tblUserData]( [UserID] [uniqueidentifier] NOT NULL, [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY] INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (5,'peter',':-D Peter ;-)') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (6,'john','J ;-)') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (7,'simon','Simon :-D') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (11,'hans','Hans :-)') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (15,'Jane','Jane3') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (28,'jean','jean') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (48,'Jack','Jack') INSERT INTO tblUserdata (UserCode,UserName,DisplayName) VALUES (51,'Andre','Andre') INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (5,11) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (5,6) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (6,11) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (6,5) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (6,15) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (7,28) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,6) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,5) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,15) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (11,48) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (15,6) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (15,11) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (15,48) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (28,7) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (48,11) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (48,51) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (48,15) INSERT INTO tblFriends (UserCodeOwner,UserCodeFriend) VALUES (51,48)
I would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).
What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
Hi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
