Thursday, September 19, 2013

[SQL Server] How to insert in 3 tables using stored procedure

[SQL Server] How to insert in 3 tables using stored procedure

How to insert in 3 tables using stored procedure

Posted: 19 Sep 2013 02:20 PM PDT

Hello Everyone and Hello WorldHow should i use stored procedure in inserting in three tables? like this one...i Have 3 tables namely Bio, Sex, and Status*BiobioIDFirstnamemiddlenamelastnamesexIDstatusID*SexsexIDsex(Male or Female only)*StatusstatusIDstatus(Single or Married or Divorce only)i have a code like this....-----------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SaveBiography] @firstname varchar(50), @middlename varchar(50), @lastname varchar(50), @sex varchar(50), @status varchar(50), @sexID int, @statusID int, @bioID int outputASBEGIN SET NOCOUNT ON; SET @firstname = LTRIM(RTRIM(@firstname)) SET @middlename = LTRIM(RTRIM(@middlename)) SET @lastname = LTRIM(RTRIM(@lastname)) SET @sex = LTRIM(RTRIM(@sex)) SET @status = LTRIM(RTRIM(@status)) IF EXISTS(SELECT * FROM Biography.dbo.Bio WHERE firstname = @firstname AND middlename = @middlename AND lastname = @lastname AND bioID <> @bioID) BEGIN RAISERROR ('[Error]Duplicate name', 16, 1) RETURN END IF @firstname = '' BEGIN RAISERROR ('[Error]No first name', 16, 1) RETURN END IF EXISTS (SELECT * FROM Biography.dbo.Bio WHERE bioID = @bioID) BEGIN UPDATE Bio SET firstname = @firstname, middlename = @middlename, lastname = @lastname, sexID = @sexID, statusID = @statusID WHERE bioID = @bioID END ELSE BEGIN IF @sex = 'Male' BEGIN @sexID = 1 END ELSE BEGIN @sexID = 2 END IF @status = 'Single' BEGIN @statusID = 1 END ELSE IF @status = 'Married' BEGIN @statusID = 2 END ELSE @status = 'Divorced' BEGIN @statusID = 3 END INSERT INTO Bio(firstname,middlename,lastname, sexID, statusID ) VALUES (@firstname, @middlename, @lastname, @sexID, @statusID ) SELECT @bioID = SCOPE_IDENTITY() END END-----------------------------------------------and i'm getting this kind of error..------------------------------------------------Msg 102, Level 15, State 1, Procedure SaveBiography, Line 41Incorrect syntax near '@sexID'.Msg 102, Level 15, State 1, Procedure SaveBiography, Line 59Incorrect syntax near '@statusID'.Msg 102, Level 15, State 1, Procedure SaveBiography, Line 67Incorrect syntax near '@statusID'.Msg 102, Level 15, State 1, Procedure SaveBiography, Line 112Incorrect syntax near 'END'.--------------------------------------------------------------Please Help me Lord:crying:

Day of week comparison for contact availability

Posted: 19 Sep 2013 12:05 PM PDT

Hey all, I have a problem I'm trying to figure out here. I am trying to figure out the availability of a contact by comparing their available flag to the current day of the week. IE, the contact has 7 BIT fields in the table,1 for each day of the week, being T or F depending on if they are available. I'm trying to figure out how to read the correct field based on the day of the week to see if their available (T) that day for a notification. Each field name in the table is as such: mon, tue, wed, etc. I can get the current DOW from sql and trim it to the same length and case of the field names to try and figure out which one I need to check whether that fields contents are true or not, but I'm stumped on where to go from here. I can basically get around sql server MOST of the time, but I am by no means a pro, and this one has me stumped. I'm sure I have to be missing something obvious here. Any help is greatly appreciated!Kevin

Ignoring parenthesis in a name field

Posted: 19 Sep 2013 09:15 AM PDT

I have a name field as [b]Dow, Jones (NewYork)[/b] and i can get the fist name with the query below as Sean suggested yesterday as[quote]select SUBSTRING(_Name, patindex('%[,]%',_Name) + 1, len(_Name)) as OwnerFirstName from CC[/quote][i]with the result as Jones (NewYork) [/i]and the last name as [quote]select substring(_Name, 0, charindex(',',_Name)) as OwnerFirstName from CC [/quote][i]with the result as "Dow"[/i]but i want the first name only as Jones i.e. ignoring the (newYork) stringany help is appreciated thanks

How to set values to a field in database using sql server 2005

Posted: 18 Sep 2013 04:20 PM PDT

Hello World:-)How should i set a values from my database like...I have 3 tables...namely Bio Table, Sex Table, Status Table*Bio TableBioIDFirstnameMiddlenameLastnamesexIDstatusID*Sex TablesexIDsex(male or female)*Status TablestatusIDstatus(single, married or divorced)question..how should i set the sex and status table, in such a way in only contains male or female,,,,or single or married or divorcePlease help me??...God Speed!!!

Why doesn't my index get used?

Posted: 19 Sep 2013 09:56 AM PDT

Hi,I had a query that I was running to get the top n records (eg, top 1,000). I changed the query so that it does row_number() and then gives back rows numbered between i and j (eg, rn between 1,001 and 2,000). My query was using an index that worked very fast (0 - 3 secs). When I made the change, it decided not to use that index anymore and now it is very slow (5 - 120 secs). I tried using >1000 and <2000 instead of between, and a few other small things that did not work. Putting in an index hint made the query run at full speed again - but this is the first time I've ever done that and as far as I understand I should undo it and make it the last. How can I make this choose my index without giving it a hint??I have so many tables and indexes involved, I am not sure what to show exactly...This is my query:[code]ALTER PROCEDURE [dbo].[sp_SFP099_NonLab_Drilldown] -- Add the parameters for the stored procedure here --@myCurr varchar(10) = NULL -- 'Mixed$' or 'Can$' @L3 varchar(50) = NULL,@L4 varchar(50) = NULL,@L5 varchar(50) = NULL,@L6 varchar(50) = NULL ,@L7 varchar(50) = NULL,@L8 varchar(50) = NULL,@L9 varchar(50) = NULL,@L10 varchar(50) = NULL,@CC varchar(50) = NULL ,@myFunc varchar(25) = NULL -- 'Engineering' or 'Mechanical' or etc. If passed blank, not used ,@myPer varchar(2) = NULL, @myYTD_1_0 bit = 0 -- the month number (eg, '8'); 1 means YTD, 0 CM only ,@numRecs bigint = 1, @expL2 varchar(4) = NULL, @primLev varchar(2) = NULL ,@desc1 varchar(100) = NULL, @desc2 varchar(100) = NULL ,@myUser varchar(50) = NULL -- info about the person who called the procedure ,@1stRec bigint = 1, @myLY_1_or_0 bit = 0 --1 means last year, 0 means current yearASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @startTime as datetime set @startTime = getdate()--compose dynamic SQL declare @myAcctKey as varchar(8) set @myAcctKey = right('00' + @primLev,2) + case when @expL2 is null then '' else right('0000' + @expL2,4) end declare @myOrg as varchar(50) = 'All' -- 'All' gives the whole company declare @myLev as varchar(20) = 'All' if @CC is not null begin set @myOrg = @CC; set @myLev = 'CC'; end else if @L10 is not null begin set @myOrg = @L10; set @myLev = '[Level 10]'; end else if @L9 is not null begin set @myOrg = @L9; set @myLev = '[Level 9]'; end else if @L8 is not null begin set @myOrg = @L8; set @myLev = '[Level 8]'; end else if @L7 is not null begin set @myOrg = @L7; set @myLev = '[Level 7]'; end else if @L6 is not null begin set @myOrg = @L6; set @myLev = '[Level 6]'; end else if @L5 is not null begin set @myOrg = @L5; set @myLev = '[Level 5]'; end else if @L4 is not null begin set @myOrg = @L4; set @myLev = '[Level 4]'; end else if @L3 is not null begin set @myOrg = @L3; set @myLev = '[Level 3]'; end; declare @totRows as bigint set @totRows = 0 declare @mySQL as nvarchar(max) set @mySQL = 'SELECT @_myOrg as [myOrg]--------THIS PART USES MY INDEX BY DEFAULT------------- ;with myRows as ( SELECT RowNum, ROW_NUMBER() OVER (ORDER BY RowNum ASC) AS rn FROM ' + case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end + ' a INNER JOIN dbo._SFP100_Org_No_Ampers e on a.[cost ctr] = e.[org id] WHERE ' + case when len(@myAcctKey) = 2 then 'left([AcctKey],2) = @_myAcctKey' else '[AcctKey] = @_myAcctKey' end --filter primary & maybe subL2 accts + case when @myLev = 'All' then '' else ' and ' + @myLev + ' = @_myOrg ' end --don't necessarily filter CC org + ' and [month] ' + case when @myYTD_1_0 = 1 then '<' else '' end + '= @_myPer ' --YTD or just CM 1 or 0 + case when @myFunc is null then '' else ' and [functional reporting] = @_myFunc ' end +') SELECT @_totRows = Count([RowNum]) from myRows------THIS PART USED THE INDEX UNTIL I CHANGED FROM TOP @_numRecs to BETWEEN @_1stRec and (@_1stRec + @_numRecs - 1), NOW ONLY USES INDEX WITH HINT---- SELECT @_totRows as [totRows] ' + ';with myRows as ( SELECT RowNum, ROW_NUMBER() OVER (ORDER BY RowNum ASC) AS rn FROM ' + case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end + ' a ' + ' WITH (INDEX(IX_CostCtr_AcctKey_Mo_RowNum)) ' + ' INNER JOIN dbo._SFP100_Org_No_Ampers e on a.[cost ctr] = e.[org id] WHERE ' + case when len(@myAcctKey) = 2 then 'left([AcctKey],2) = @_myAcctKey' else '[AcctKey] = @_myAcctKey' end --filter primary & maybe subL2 accts + case when @myLev = 'All' then '' else ' and ' + @myLev + ' = @_myOrg ' end --don't necessarily filter CC org + ' and [month] ' + case when @myYTD_1_0 = 1 then '<' else '' end + '= @_myPer ' --YTD or just CM 1 or 0 + case when @myFunc is null then '' else ' and [functional reporting] = @_myFunc ' end +') , myTopRowNums as ( SELECT [RowNum] from myRows where rn between @_1stRec and (@_1stRec + @_numRecs - 1) ) SELECT a.[CC] , a.[Account] , a.[Vendor] , a.[Doc Header Text] as [Doc] , a.[User Text] as [UserText] , a.[Description] , a.[Year] , a.[Month] , a.[Quantity] , a.[Unit] , a.[Dollar Amount] as [DollarAmount] FROM ' + case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end + ' a INNER JOIN myTopRowNums b on a.[RowNum] = b.[RowNum] ORDER BY a.[RowNum]' PRINT @mySQL --execute dynamic SQL (using sp_executesql instead of exec(@sql) means not vulnerable to sql injection) EXEC sp_executesql @mySQL, N'@_myOrg varchar(50), @_myFunc varchar(25), @_myPer varchar(2),@_myAcctKey varchar(8) ,@_numRecs bigint, @_expL2 varchar(4), @_primLev varchar(2), @_1stRec bigint, @_totRows bigint output', @_myOrg = @myOrg, @_myFunc = @myFunc, @_myPer = @myPer, @_myAcctKey = @myAcctKey ,@_numRecs = @numRecs, @_expL2 = @expL2, @_primLev = @primLev, @_1stRec = @1stRec, @_totRows = @totRows outputEND[/code]The index that was being used is a non-clustered index defined like this (my NonLab_Read synonym points at either an A or B table):[code]CREATE NONCLUSTERED INDEX [IX_CostCtr_AcctKey_Mo_RowNum] ON [dbo].[_SFP099_NonLabB] ( [Cost Ctr] ASC, [AcctKey] ASC, [Month] ASC, [RowNum] ASC)[/code]When I don't give the hint, it now wants to use a clustered index, even though the only thing it gets from there is the cost center #:[code]CREATE CLUSTERED INDEX [CX_Cost_Ctr_Cost_Elem] ON [dbo].[_SFP099_NonLabB] ( [Cost Ctr] ASC, [Cost Elem] ASC)[/code]I'm at a loss here... Can anyone see based on this what might make my preferred index be used without the hint? Are there other things I should post to help the experts see the answer? Should I just go ahead and use the hint?Thanks for the help.Tai

Run a query against registered servers and insert results into table

Posted: 01 Mar 2012 07:52 PM PST

Hi all, Is it possible to run a query against multiple registered servers and have the results inserted into a table? Trying to get details of SQL boxes and wanted to keep the data dynamic and always up to date. So id like to run say[code="sql"]SELECT @@VERSION AS [SQL Server and OS Version Info];[/code]Thanks S

Relationship Question

Posted: 19 Sep 2013 01:24 AM PDT

Hey guys - I have a relationship question....for SQL Server that is. ;)I'm in the early stages of my first large "ETL" project which I'll take data from a lot of old tables and insert them into a more normalized design. I have a little test database which I'll test out new scripts on..over and over. [i]I would like the ability to run a SP to clear out all the test tables in preparation for the next test. [/i] So, after creating a "dump' SP, I ran into some very basic, fundamental table relationship problems. As a test, I have two basic tables. See screenshot from SSMS. Very common relation set up there.[img]http://www.mvdb.virginia.gov/assets/images/sqlscreenshot.jpg[/img]I have set up a couple integrity rules: Set the FK in "NewCustomers" to go to it's default value of 0 in the event that the child record (NewAccounts) is deleted. Cascade the changes to NewCustomers in the unlikely event that the PK in NewAccounts is changed. Seems logical and common practice. Right??So with both tables populated with test data, I get constraint errors when I try to DELETE or TRUNCATE TABLES.So when I try to (in this order):DELETE FROM NewAccounts (and truncate)DELETE FROM NewCustomers (and truncate)I get:[font="Courier New"][color=#ff0000]The DELETE statement conflicted with the FOREIGN KEY constraint "FK_NewCustomers_NewAccounts". The conflict occurred in database "AcmeCars", table "dbo.NewAccounts", column 'NewAccountID'.The statement has been terminated.[/color][/font]It still deletes all the Customers but keeps the Accounts.I thought by deleting the accounts FIRST, it would set the FKs all to "0" and then turn around and delete the Customers. I must be missing an obvious concept. Can you enlighten?

SQL Server 2000 upgrade issue...

Posted: 19 Sep 2013 02:06 AM PDT

I'm attempting to migrate SQL Server 2000 database to SQL Server 2008. After reviewing the results of the upgrade advisor (no issues), I restored the database to 2008. It was subsequently identified that one of the fields had its length changed from 30 to 10 (nvarchar!10), null). My experience with upgrades is limited (first time effort). Is there something I missed when moving the database over? Copied the backup to the new environment and restored the database.Any comments / URLs would be appreciated. Thanks.

Adding User to SQL Server

Posted: 05 Sep 2013 02:13 AM PDT

Hi,I got a scenario where I am using [b]Windows Application[/b].I want to create a SQL Server User satisfying the password policy. I have couple of questions related to it.1. How can I create such a user?2. After creation of the user, I want to store its password somewhere at a central location[don't know where? can somebody help?]. While connecting to DB, read this password from central location on the network and login to SQL Server.Please help.Thanks,Paresh
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog