TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
srikanth divvela
NA
2
1.4k
I Have 3 Input Parameters,Based On Values Of Each Input Para
Feb 14 2014 5:02 AM
I Have 3 Input Parameters,Based On Values Of Each Input Parameter I Have To Prepare The Sql In Procedure Dynamically ..in that i got error .emp id null values are passed
create procedure sri_sp
@Name VARCHAR(100),
@Status VARCHAR(100),
@PanNo VARCHAR(100),
@DateOfLeaving VARCHAR(100),
@Designation VARCHAR(100),
@NoOfDays VARCHAR(100),
@BankName VARCHAR(100),
@BankBranchName VARCHAR(100),
@AccountNo VARCHAR(100),
@EPFNo VARCHAR(100),
@Basic VARCHAR(100),
@HRA VARCHAR(100),
@Conveyance VARCHAR(100),
@Medical VARCHAR(100),
@OtherAllowance VARCHAR(100),
@SpecialAllowance VARCHAR(100),
@incentive VARCHAR(100),
@bonus VARCHAR(100),
@Arrears VARCHAR(100),
begin
DECLARE @empid
DECLARE @Name varchar(100)
DECLARE @panno varchar(100)
DECLARE @location VARCHAR(100)
DECLARE @SQLQuery VARCHAR(500)='SELECT EMPId FROM Employees '
DEClARE @WhereClause VARCHAR(500) = ''
if(@Name is null and @panno is null and @location is null)
BEGIN
SELECT -1
return
END
if(@Name is not null)
BEGIN
SET @WhereClause=@WhereClause+' Name='''+@Name+''''
END
if(@PanNo is not null)
BEGIN
IF @WhereClause IS NULL OR @WhereClause = ''
BEGIN
SET @WhereClause=' PanNo='''+@panno+''''
END
ELSE
BEGIN
SET @WhereClause=@WhereClause+' and PanNo='''+@panno+''''
END
END
if(@location is not null)
BEGIN
IF @WhereClause IS NULL OR @WhereClause = ''
BEGIN
SET @WhereClause=' location='''+@location+''''
END
ELSE
BEGIN
SET @WhereClause=@WhereClause+' and location='''+@location+''''
END
END
IF @WhereClause IS NOT NULL AND @WhereClause <> ''
BEGIN
SET @SQLQuery = @SQLQuery + ' WHERE ' + @WhereClause
END
EXECUTE sp_executesql @SQLQuery ,N'@empid int OUTPUT',@empid OUTPUT;
select @empid
DECLARE @EMPSALRYID INT=(SELECT COUNT(EmpId) FROM Employees WHERE EmpId=@empid)
IF(@empid=0 or @empid=null)
BEGIN
-- INSERT EMPLOYEE DETAILS
INSERT INTO Employees(Name,PanNo, BankName, BankBranchName, AccountNo,Location,Status,Designation, EPFNo,DateOfLeaving, ESI)
VALUES(@Name,@PanNo,@BankName, @BankBranchName, @AccountNo,@Location,Convert(char,@Status),@Designation, @EPFNo, CONVERT(date,@DateOfLeaving,3),convert(bit,@ESI))
Set @empid=(select @@IDENTITY)
END
ELSE
BEGIN
--UPDATE EMPLOYEE DETAILS
UPDATE Employees
SET Name=@Name,
PanNo=@PanNo,
BankName=@BankName,
BankBranchName=@BankBranchName,
Location=@Location,
Status=convert(char,@Status),
Designation=@Designation
,EPFNo=@EPFNo,
DateOfLeaving= CONVERT(date,@DateOfLeaving,3),
ESI=Convert(bit,@ESI)
WHERE EmpId=@empid
END
Reply
Answers (
0
)
how to kill a project after one year of installation
Console Application HELP