May 15, 2012 at 11:47 pm
currently i have multiple sql statements for different table executed in the front end(asp.net)
the querys are if exist(select.........)
update..............
else
insert(...............)
from the front end its working fine
i am creating a single SP for the same , when calling it in the frontend how can i pass the multiple values for different tables ? & execute it
or do i need to create different SPs for different tables?
i alo need to add in begin trans & end trans so that either all teh queries gets executed or none
May 16, 2012 at 1:36 am
Three or four choices:
1. Create separate SPs and call the correct one from the front end depending on the table.
2. Create separate SPs and a master SP that gets passed the table name then calls the correct SP.
3. Create the SQL in the SP in a string and then use dynamic SQL to execute it.
4. Create one SP that gets passed a table name. In that, execute (static SQL) the appropriate code block depending on the table name.
I would probably choose door #2.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 16, 2012 at 1:46 am
May 16, 2012 at 2:33 am
hi
currently in my front end all this sql statements gets executed on a single button click where in i have begin trans & end trans so that either all the statements gets executed or none are executed
if i need to create a separate SP then is not my current front end sql statements ok???
May 16, 2012 at 6:28 pm
ssurekha2000 (5/16/2012)
hicurrently in my front end all this sql statements gets executed on a single button click where in i have begin trans & end trans so that either all the statements gets executed or none are executed
if i need to create a separate SP then is not my current front end sql statements ok???
Personally I'd put the BEGIN/COMMIT TRANSACTION statements into the SPs to keep the transaction open for a minimum period of time. Yet another reason to choose what's behind Door #2 because then it would be relatively easy to encapsulate all the SP calls (or only the single calls) into a transaction (although you could do this with the other options as well).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 16, 2012 at 11:59 pm
ok
culd u give me a sample SP for option 2
with Begin & commit trans
May 17, 2012 at 12:06 am
ssurekha2000 (5/16/2012)
okculd u give me a sample SP for option 2
with Begin & commit trans
Probably but first I'd ask you to provide me with:
1. DDL for two sample tables and DML to populate them with some sample data.
2. A better idea of the operation being performed by your front end (e.g., UPDATE one or more rows in the sample tables) including the SQL UPDATE or whatever.
3. A clear idea of the condition where it updates either table #1 or table #2.
The reason for #3 is that I'm confused whether you want to update only one table or both tables on a single click of your front end application.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 12:24 am
IF EXISTS (SELECT 1 FROM E_TABLE1 WHERE eid =@eid)
UPDATE E_TABLE1
SET FNAME=@FNAME,MNAME=@MNAME,LNAME=@LNAME,DOB=@DOB,Address=@Address, Gender=@Gender,Phone=@Phone,
Mobile=@Mobile,Email=@Email,
Where eid =@eid
ELSE
INSERT INTO E_TABLE1(eid,FNAME,MNAME,LNAME,DOB,Address,Gender,Phone,Mobile,Email)
VALUES (@eid,@FNAME,@MNAME,@LNAME,@DOB,@Address,@Gender,@Phone,@Mobile,@Email)
IF EXISTS (SELECT 1 FROM E_TABLE2 WHERE eid =@eid)
UPDATE E_TABLE2
SET desig=@desig,dept=@dept,DOJ=@DOJ
Where eid =@eid
ELSE
INSERT INTO E_TABLE2(eid,desig,dept,DOJ)
VALUES (@eid,@desig,@dept,@DOJ)
May 17, 2012 at 12:31 am
I still need the DDL for E_TABLE1 and E_TABLE2 plus the sample data.
I also now need to know how you plan to pass in the values you're updating from (XML document perhaps?).
Lastly, the EXISTS/UPDATE/INSERT is no longer necessary in SQL 2008 - use MERGE instead.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 12:45 am
CREATE TABLE [dbo].[E_TABLE1 ](
[ROWID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[eid] [varchar](50) NOT NULL,
[FNAME] [varchar](50) NOT NULL,
[MNAME] [varchar](50) NOT NULL,
[LNAME] [varchar](50) NOT NULL,
[DOB] [smalldatetime] NULL,
[Address] [varchar](50) NOT NULL,
[Gender] [varchar](1) NOT NULL,
[Phone] [varchar](10) NOT NULL,
[Mobile] [varchar](10) NOT NULL,
[Email] [varchar](50) NOT NULL,
CONSTRAINT [PK_E_TABLE1 ] PRIMARY KEY CLUSTERED
(
[eid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[E_TABLE2](
[ROWID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[eid] [varchar](50) NOT NULL,
[DOJ] [smalldatetime] NULL,
[Dept] [varchar](50) NOT NULL,
[Desig] [varchar](50) NOT NULL,
) ON [PRIMARY]
GO
i need that alll the tables shld get executed or none of them
i am passing the values from asp.net page textcontrols
how to use merge?? as i have been using update & insert ??
May 17, 2012 at 12:53 am
This is actually going to be a bit easier than I initially thought because I thought you only wanted to update one (of many) tables based on conditions being set in the front end.
Furthermore, there's no need to use multiple SPs as that will just complicate things.
All you'll need to pass into the SP are the fields to update, then complete the MERGE statements within the transaction.
I can give you a skeleton based on the DDL you provided (although some sample data would still be helpful), you just need to give me a little bit of time as I'm working on something else I need to finish in the next couple of hours.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 1:18 am
hi
i have actually created a single SP with all the sql statements for multiple tables( sample o 2 given to you)
my doubt reg the same
i have different parameter variables for different tables defined in teh SP
i knw using a single table & passing parameters to it from front end page
suppose i need to update or insert Name & age in table student using SP
i will call teh SP in the web page & simply pass as
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", txtname.Text);
cmd.Parameters.AddWithValue("age", txtage.Text);
this is for a single tabel
now with SP with multiple tables & its params do i need to pass in the same way
& ow do i ue mERGE & begin & end trans
May 17, 2012 at 3:21 am
Assuming you're working in SQL 2008 (because this is the SQL 2008 forum), the following should be a good example to work from:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.UpdateE_TABLEs
@eidINT= NULL
,@FNAMEVARCHAR(50)= NULL
,@MNAMEVARCHAR(50)= NULL
,@LNAMEVARCHAR(50)= NULL
,@DOBSMALLDATETIME= NULL
,@AddressVARCHAR(50)= NULL
,@GenderVARCHAR(1)= NULL
,@PhoneVARCHAR(10)= NULL
,@MobileVARCHAR(10)= NULL
,@EmailVARCHAR(50)= NULL
,@desigVARCHAR(50)= NULL
,@deptVARCHAR(50)= NULL
,@DOJSMALLDATETIME= NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @rc INT
IF @eid IS NULL RETURN -1
BEGIN TRANSACTION T1;
BEGIN TRY
-- First MERGE to E_TABLE1
;WITH E_TABLE1_Updates
(eid, FNAME, MNAME, LNAME, DOB, Address, Gender, Phone, Mobile, Email) AS (
SELECT @eid, @FNAME, @MNAME, @LNAME, @DOB, @Address, @Gender, @Phone, @mobile, @Email)
MERGE INTO E_TABLE1 t
USING E_TABLE1_Updates s ON s.eid = t.eid
WHEN MATCHED THEN
UPDATE
SET FNAME= s.FNAME
,MNAME= s.MNAME
,LNAME= s.LNAME
,DOB= s.DOB
,Address= s.Address
,Gender= s.Gender
,Phone= s.Phone
,Mobile= s.Mobile
,Email= s.Email
WHEN NOT MATCHED BY TARGET THEN
INSERT (eid, FNAME, MNAME, LNAME, DOB, Address, Gender, Phone, Mobile, Email)
VALUES(s.eid, s.FNAME, s.MNAME, s.LNAME, s.DOB, s.Address, s.Gender, s.Phone, s.Mobile, s.Email);
-- Second MERGE to E_TABLE2
;WITH E_TABLE2_Updates
(eid, desig, dept, DOJ) AS (SELECT @eid, @desig, @dept, @DOJ)
MERGE INTO E_TABLE2 t
USING E_TABLE2_Updates s
ON s.eid = t.eid
WHEN MATCHED THEN
UPDATE
SET eid= s.eid
,desig= s.desig
,dept= s.dept
,DOJ= s.DOJ
WHEN NOT MATCHED BY TARGET THEN
INSERT (eid, desig, dept, DOJ) VALUES(s.eid, s.desig, s.dept, s.DOJ);
END TRY
BEGIN CATCH
SET @rc = ERROR_NUMBER()
ROLLBACK TRANSACTION T1;
RETURN @rc
END CATCH
COMMIT TRANSACTION T1
RETURN 0
END
GO
INSERT INTO E_TABLE1
(eid, FNAME, MNAME, LNAME, DOB, Address, Gender, Phone, Mobile, Email)
VALUES (1, 'Dwain', 'X', 'C', '1958-06-18', 'Not to be disclosed', 'M', 'N/A', 'N/A', 'dwain@myspace.com')
INSERT INTO E_TABLE2 (eid, desig, dept, DOJ)
VALUES (1, 'XXX', 'YYY', '2012-01-01')
SELECT * FROM E_TABLE1
SELECT * FROM E_TABLE2
EXEC dbo.UpdateE_TABLEs
1-- @eid
,'Dwaine'-- @FNAME
,'Guru'-- @MNAME
,'C--'-- @LNAME
,'1958-06-18'-- @DOB
,'Secret'-- @Address
,'M'-- @Gender (no change)
,'5551212'-- @Phone
,'5553434'-- @mobile
,'dwain@myemail.com'-- @Email
,'BBB'-- @desig
,'CCC'-- @dept
,'2012-02-01'-- @DOJ
EXEC dbo.UpdateE_TABLEs
2-- @eid
,'Jack'-- @FNAME
,'B'-- @MNAME
,'Nimble'-- @LNAME
,'1960-07-12'-- @DOB
,'Home'-- @Address
,'M'-- @Gender (no change)
,'5554444'-- @Phone
,'5552222'-- @mobile
,'jack@myemail.com'-- @Email
,'AAA'-- @desig
,'DDD'-- @dept
,'2012-03-01'-- @DOJ
SELECT * FROM E_TABLE1
SELECT * FROM E_TABLE2
If for some reason you're in SQL 2005, you'll need to replace the MERGE statements with your (ugly) IF EXISTS/UPDATE/INSERT statements.
Note that the SP returns:
0 - if there were no errors
-1 - if you pass a NULL @eid
### - The SQL exception code (number) trapped by the TRY/CATCH
Hope this helps but please don't ask me how to translate the SP EXEC calls to your .Net front end code!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 3:46 am
Sorry but I have one other small, nit-picking question.
Why are you using SMALLDATETIME datatype for DOB and DOJ (date of job start?)? Why not use instead the SQL 2008 DATE datatype?
Sorry, that's 3 questions.:-P
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 7:51 pm
it nothing like that
in some tables i do use date datatype but i am really not aware abt the differences in using this different datatype
i was just concerned abt storing date
whts the difference betwen actually using date, datetime & smalldatetime
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply