Share via

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. and Incorrect syntax near ','.

Question

Wednesday, January 19, 2011 3:31 AM

 hi

 i am writing storedprocedure for creating salary table ,but getting error while executing sp.Please help me with this error.

Thanks.

my sp is:

 

USE [Crystal.HRM]
GO
/****** Object:  StoredProcedure [dbo].[Hrm_Salary]    Script Date: 01/19/2011 13:11:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Hrm_Salary](
@Pay_Basic char(1),
@Pay_Hra char(1)
)
 
AS
BEGIN
declare @fields nvarchar(max)
set @fields='Hrm_Emp_Id [int] not null'
if(@Pay_Basic='Y')
Begin
set @fields=@fields+', Pay_Basic [int] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Basic]  DEFAULT ((0)),'
end
if(@Pay_Hra='Y')
Begin
set @fields=@fields+', Pay_Hra [smallint] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Hra]  DEFAULT ((0)),'
End
set @fields=substring(@fields,0,len(@fields)-2)
declare @sql nvarchar(max)
set @sql='CREATE TABLE [dbo].[Salary]('+
@fields+'
CONSTRAINT [PK_Payroll] PRIMARY KEY CLUSTERED
(
[Pay_Emp_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Crystal.HRM]
GO
ALTER TABLE [dbo].[Hrm_Emp_Salary]  WITH CHECK ADD  CONSTRAINT [FK_Hrm_Emp_Salary_Hrm_Emp_Tbl] FOREIGN KEY([Pay_Emp_Id])
REFERENCES [dbo].[Hrm_Emp_Tbl] ([Emp_Id])'
exec sp_ExecuteSql @sql
END

 

 

All replies (3)

Wednesday, January 19, 2011 4:06 AM âś…Answered

I think you are setting comma ',' two times.

Remove ',' from if(@Pay_Hra) condition. change code as below and try it.

if(@Pay_Basic='Y')
Begin
set @fields=@fields+', Pay_Basic [int] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Basic]  DEFAULT ((0)),'
end
if(@Pay_Hra='Y')
Begin
set @fields=@fields+' Pay_Hra [smallint] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Hra]  DEFAULT ((0)),'
End
 

Wednesday, January 19, 2011 4:16 AM

previous comment deleted as it is not a case of CTE!

just remove extra comma as RaviKaria said like

if(@Pay_Basic='Y')
Begin
    set @fields=@fields+', Pay_Basic [int] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Basic]  DEFAULT ((0))'
end
if(@Pay_Hra='Y')
Begin
    set @fields=@fields+', Pay_Hra [smallint] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Hra]  DEFAULT ((0))'
End

Wednesday, January 19, 2011 4:25 AM

writing storedprocedure for creating salary table ,but getting error while executing sp

 

1 Is there any need to create SQLServer table or you can create sql server temp table ??

2. Suppose this SP will be called by 2 user then this sp can not able to create two table with same name.

3.  I will recommand you temp table approch or table variable approch. Reference Link = http://www.sqlteam.com/article/temporary-tables

 

  • Last updated on