Hi, Try like this... If you need add TRANSACTION also in this Code.

CREATE PROCEDURE addTitle
@ErrorDtls VARCHAR(100) OUTPUT
BEGIN
BEGIN TRY
-- Insert Statement
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
SELECT @ErrorDtls = NULL
END TRY
BEGIN CATCH -- While Error this Block will work
-- Insert Error table
Insert into apl_Error(Error_Desc,Error_Date)
SELECT RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(), GETDATE()
-- Frame Error Message
SELECT @ErrorDtls ='Error on Inserting'

END CATCH

END

-- Execute Statement
DECLARE @ErrorDtls VARCHAR(100)
EXEC addTitle @ErrorDtls=@ErrorDtls OUTPUT
SELECT @ErrorDtls

GVPRabu

What nested BEGIN and END..? used in your Stored Procedure.

Hi, Check the Script

SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.EmpID ORDER BY M.EmpID)) 'RowNumber', M.EmpID, M.Leave_Year, M.Taken, M.Credit,
(SELECT SUM((ISNULL(Balance,0.0))
FROM Table_Name WHERE ID<=M.ID AND EmpID=M.EmpID)
FROM Table_Name M
ORDER BY M.EmpID

Why I got down vote for my Answer...? can you help me in this query[^] create a function that give comma separated value from a string[^] updating a string or a word from a column from SQL Server[^] Like many time my answers are got down voted. If anyone will give the comments then down voted means we will improve our answering way. Simply without any mistakes and reasons giving down vote then it will spoiled our Interest in Code Project. So please take some action in this Issue. Regards, GVPrabu

Hi, You need to insert same details to another table right, Use same Same logic. No need to assign a values in variable.

What is the logic for finding @RMNGSTS Values?

Hi Subbarao, I will give you some sample DB design Document by Monday.... :) :) :)

Hi... With my answer I can't able to explain the DB design. First you need to collect the requirements, It will be different as per the vendor and many constraints will be there. Only Check the links.... The Basics of Good Database Design in Web Development[^] eCommerceDatabse.png[^] Regards, GVPrabu

Hi, Use DEFAULT Constraint.... ALTER TABLE visits MODIFY closed_date DATETIME NOT NULL DEFAULT NOW(); Check the following link MySQL ALTER Command[^]

Hi You will give the access rights to that user through TSQL or SQL Server Management Studio... Check the following links Script to determine permissions in SQL Server 2005[^] Configuring user permissions for Microsoft SQL Server[^] Assigning permissions and roles in SQL Server 2005/2008 databases[^]

Hi Its simple Use COUNT Function... like as follows

SELECT CAST(ISNULL(COUNT(ProdType),0) AS VARCHAR(10)) + ' '+ ProdType FROM ProdDtls GROUP BY ProdType

Regards, GVPrabu

Hi, You can use Column Alias like as follows

SELECT V.MeaninglessColumnNameImposedBySystem1 [First Name],
V.MeaninglessColumnNameImposedBySystem2 [Last Name],
V.MeaninglessColumnNameImposedBySystem3 [Address 1],
...
FROM LongViewName AS V
WHERE etc=@etc

for best practice go for Column names without any space like FirstName, LastName, Address1 ... Regards GVPrabu

Hi, Check the following Books. -- Book 1 Book Name : Beginning SQL Server 2008 for Developers From Novice to Professional Jul 2008 Authors : Louis Davidson, With Kevin Kline, Scott Klein, and Kurt Windisch Publisher : APress -- Book 2 Book Name : Pro-T-SQL-2008-Programmers-Guide-Experts-Voice-in-SQL-Server Author : Michael Coles Publisher : APress Regards, GVPrabu

Hi, I think You are asking about "IDENTITY" Property in SQL Server right. If you will use IDENTITY while creating table, It will Increment Automatically based on your Starting value and Increment. For Example,

CREATE TABLE #T1 (ID INT NOT NULL IDENTITY(1,1), Name VARCHAR(12))
INSERT INTO #T1(Name) VALUES('A')
SELECT * FROM #T1

BEGIN TRAN T1
INSERT INTO #T1(Name) VALUES('B')
ROLLBACK TRAN T1

INSERT INTO #T1(Name) VALUES('C')
SELECT * FROM #T1

So Once Identity values is Incremented, In case If Transaction Roll backed also you will get next value only. Regards, GVPrabu.

Hi Naina, Check the Script, U can use CASE Statement in UPDATE.

CREATE TABLE #EmpDtls(ID INT, EmpGender CHAR(1))
INSERT INTO #EmpDtls (ID, EmpGender) VALUES (1,'M'),(2,'F'),(3,'M')

SELECT ID, EmpGender FROM #EmpDtls
-- Update Statement
UPDATE #EmpDtls SET EmpGender = (CASE WHEN EmpGender='M' THEN 'F' WHEN EmpGender='F' THEN 'M' END)

SELECT ID, EmpGender FROM #EmpDtls

Regards, GVPrabu

Hi, -- DELETE, TRUNCATE and DROP Statements DELETE /* The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. */ TRUNCATE /* TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. */ DROP /* The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. */ --Difference between TRUNCATE and DELETE commands /* 1) TRUNCATE is a DDL command whereas DELETE is a DML command. 2) TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace. Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data. 3) You cann't rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently. 4) In case of TRUNCATE ,Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired. 5) You cann't use conditions(WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause */ Regards, GVPrabu

-- Multiple Update using Joins

-- Create temp table for Teste Update Statement
CREATE TABLE EmpDtls(Code VARCHAR(10), Name VARCHAR(100), DeptCode TINYINT)
CREATE TABLE DeptDtls(DeptCode TINYINT, DeptName VARCHAR(50), EmpCount INT)

-- Insert Sample Values
INSERT INTO EmpDtls(Code, Name, DeptCode)
VALUES('Emp1','Venkat', 1), ('Emp2','Prabu', 2), ('Emp3','Kumar', 1), ('Emp4','Karthick', 3),
('Emp5','Amith', 2),('Emp6','HariKrishna', 2)

INSERT INTO DeptDtls(DeptCode, DeptName)
VALUES(1, 'IT'),(2, 'Sales'), (3,'HR'), (4, 'Accounts')

-- Check the sample values
SELECT Code, Name, DeptCode FROM EmpDtls
SELECT DeptCode, DeptName,EmpCount FROM DeptDtls

-- Update Employees Count
UPDATE D SET D.EmpCount= E.EmpCount
FROM DeptDtls D
INNER JOIN (SELECT DeptCode, COUNT(Code) 'EmpCount' FROM EmpDtls GROUP BY DeptCode) E ON E.DeptCode=D.DeptCode

SELECT DeptCode, DeptName,EmpCount FROM DeptDtls

-- Drop Table
IF OBJECT_ID('EmpDtls') IS NOT NULL DROP TABLE EmpDtls
IF OBJECT_ID('DeptDtls') IS NOT NULL DROP TABLE DeptDtls

Hi, Check the following Script, It will be use full for ur Query..........

SET NOCOUNT ON
-- Declaration
DECLARE @Employee TABLE(ID INT , EmpName VARCHAR(40))
DECLARE @I INT, @Count INT, @Temp VARCHAR(50)

SET @I = 1
-- Data
INSERT INTO @Employee(ID,EmpName)
SELECT 123,'Prabu'
UNION ALL SELECT 234, 'Raja'
UNION ALL SELECT 236, 'Kartik'
UNION ALL SELECT 1234, 'Venkat'

-- TSQL Script
SELECT @Count=COUNT(*) FROM @Employee
PRINT '~'
PRINT 'Employee Details'
PRINT '~'
WHILE @I <= @Count
BEGIN
WITH Emp AS
(
SELECT row_number() OVER ( ORDER BY ID ) 'RowNum', ID, EmpName
FROM @Employee
)
SELECT @Temp =CAST(ID AS VARCHAR) +' - '+EmpName
FROM Emp
WHERE RowNum =@I
PRINT @Temp
SET @I=@I+1
END
PRINT '~~~~~~~~~~~~~~~~~'
SET NOCOUNT OFF

Hi.... Try This....

DECLARE @Temp TABLE(Name VARCHAR(20),Age TINYINT, Total INT)

INSERT INTO @Temp(Name,Age,Total)
SELECT 'Ram',26,800
UNION ALL
SELECT 'kumar',36,300
UNION ALL
SELECT 'Sam',34,200

DECLARE @SQLStr VARCHAR(8000)
SET @SQLStr = ''

SELECT @SQLStr = @SQLStr + 'Name:' + Name + ' Age:' + CAST(Age AS VARCHAR) + ' Total:' + CAST(Total AS VARCHAR) + '; '
FROM @Temp

PRINT @SQLStr