October 27, 2016 at 11:54 pm
Hi All,
I want to create a store procedure which will contain a insert query followed by a select query which will pull the data and again followed by insert query which will use the select query values.
So please help how can i write the procedure.
Thanks in advance.
October 28, 2016 at 12:07 am
Your description of the problem is a bit vague.
Which table will you be inserting data into? What set of parameters are you expeting? Where will the select statements pull the data from?
-- Gianluca Sartori
October 28, 2016 at 12:13 am
Consider two tables
Login (Table 1)
uid
username
pwd
Profile (Table 2)
id
uid
fname
lname
Create a store procedure
1. insert a row in login table
2. get the uid from login table
3. insert row in profile table in which uid value will get from step 2.
All the above 3 steps to be done in a single store procedure.
Thank you.
October 28, 2016 at 12:35 am
You can use my stored procedure template here: https://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/
Then you just have to add the INSERT and SELECT statements that you need. Actually, with composable DML, you can achieve the thing in a single statement.
Something like this:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE InsertLogin
@username nvarchar(50),
@pwd nvarchar(50),
@fname nvarchar(50),
@lname nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
SET @localTran = 1
BEGIN TRANSACTION LocalTran
END
BEGIN TRY
INSERT INTO [Profile] (uid, fname, lname)
SELECT uid, @fname, @lname
FROM (
INSERT INTO [Login] (username, pwd)
OUTPUT INSERTED.*
SELECT 1, @username, @pwd
) AS src;
IF @localTran = 1 AND XACT_STATE() = 1
COMMIT TRAN LocalTran
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @localTran = 1 AND XACT_STATE() <> 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
BTW, you seem to be willing to store passwords in the database. I urge you not to do so: store the salted hashes only. Never, ever store passwords in the database, nor in plaintext, nor encrypted.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply