SQL Server :: Incorrect Syntax Near The Keyword "with" - If Statement Is A Common Table Expression

Jan 19, 2011

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

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

View 3 Replies


Similar Messages:

SQL Server :: Incorrect Syntax Near The Keyword 'else'?

Mar 26, 2011

I am unable to find out what's wrong in this stored procedure...it says Incorrect syntax near the keyword 'else'.

07/09/2010 WEGMANS #098 Q02 STATE COLLEGE PA
$5.96

07/12/2010 WAL-MART #1640 QPS STATE COLLEG PA
$21.07

You are sharing a picture (58.7 KB) with 2venkatesh

cutie.jpg 2venkatesh declined the offer 2venkatesh is busy. You are sharing a picture (107.4 KB) with 2venkatesh

151377447_1d4ec57c68.jpg 2venkatesh declined the offer.You are sharing a picture (58.7 KB) with 2venkatesh

cutie.jpg 2venkatesh declined the offer

2venkatesh who are you buddy in my gtalk? who are the people in those pics?

12860701286070128607012860701286070128607012860701286070128607012860701286070[code]...

View 2 Replies

SQL Server :: Incorrect Syntax Near The Keyword 'distinct'

Dec 8, 2010

I have the data below:

id locationid cookieid username IPAddress createdate
1 704 192.168.0.103 2010-12-08 18:11:24.423
2 704 192.168.0.103 2010-12-08 18:13:26.133
3 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:13:47.710
4 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:16:42.730
5 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:17:00.433
6 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:18:09.867
7 1504 myname@hotmail.com 192.168.0.103 2010-12-08 18:26:01.447

Now, I want to select all unique locationid's from a single username, its important that the results can be PAGED and SORTED, I now have:

SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY createdate) as RowNum,
distinct(locationid), l.*
FROM location_views lv
INNER JOIN locations l on l.id=lv.locationid
where lv.username='myname@hotmail.com'
) as info
WHERE RowNum > 0 AND RowNum <= 100

But this throws the error: "Incorrect syntax near the keyword 'distinct'."

View 2 Replies

Web Forms :: Incorrect Syntax Near Keyword Between

Mar 26, 2012

When I use single text box for date it work fine but when a'm check between two dates it gives error this is my code

Dim adp As New SqlDataAdapter("select EmplID='" + TextBox1.SelectedValue + "', date between ='" &
TextBox2.Text & "' and '" & TextBox3.Text & "' ,RESULT = CASE WHEN exists(select * from AtdRecord where RecDate between ='" & TextBox2.Text & "' and '" & TextBox3.Text & "'  and EmplID='" + TextBox1.SelectedValue + "')
THEN ('P')ELSE ('A ' ) END,ResultType= CASE WHEN exists (select * from AtdRecLeave where StDate between = '" + TextBox2.Text & "'and '" & TextBox3.Text & "' and EmplID='" + TextBox1.SelectedValue + "')
THEN (SELECT ResultType FROM AtdRecLeave WHERE EmplID='" + TextBox1.SelectedValue + "'
and stDate between = '" + TextBox2.Text & "' and '" & TextBox3.Text & "') else ('-') END", con)eror=

Incorrect syntax near the keyword 'between'.Incorrect syntax near '='.Incorrect syntax near '='.Incorrect syntax near '='.

View 1 Replies

SQL Server :: Incorrect Syntax Near The Keyword "union" Error

Feb 16, 2011

my statment is: SELECT TOP (20) PERCENT GroupName from [Group] ORDER BY NewID() union SELECT TOP (10) PERCENT GroupName from [Group] i get this error

View 1 Replies

SQL Server :: Incorrect Syntax Near The Keyword "FETCH"

Feb 14, 2011

i am getting error 'Incorrect syntax near the keyword 'FETCH'. ' while creating trigger.pls give solution for this error

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER Hrm_Emp_Leave_Insert_Data
ON Hrm_Leave_Lookup
AFTER INSERT
AS
BEGIN
DECLARE @EMP_ID int
DECLARE @LEAVE_ID int
DECLARE @LEAVE_TOTAL tinyint
DECLARE @LEAVE_CARRY_DAYS tinyint
select @LEAVE_ID=Leave_Id, @LEAVE_TOTAL=Leave_Total, @LEAVE_CARRY_DAYS=Leave_Carry_Days from inserted
DECLARE SelectCursor CURSOR
FOR select Emp_Id from Hrm_Emp_Tbl where Emp_Status='A'
OPEN SelectCursor
FETCH NEXT from SelectCursor into @EMP_ID
while @@FETCH_STATUS=0
BEGIN
insert into hrm_emp_leave (@EMP_ID, @LEAVE_ID, @LEAVE_TOTAL, @LEAVE_CARRY_DAYS)
FETCH NEXT from SelectCursor into @EMP_ID
END
CLOSE SelectCursor
DEALLOCATE SelectCursor
END

View 2 Replies

C# - Why Get An "Incorrect Syntax Near The Keyword INNER'' Error

Mar 11, 2011

In SQL Query Analyzer I run this statement and I don't get anny error:

string select = "SELECT " +
"aanvrager.werknemersnaam AS melder , hd_aanvragen.aanvraag_titel, " +
"hd_aanvragen.aanvraag_omschrijving, hd_aanvraag_fase.fase_datum, " +
"hd_melding_niveau_1.niveau_omschrijving AS 'Niveau 1', " +
"hd_melding_niveau_2.niveau_omschrijving AS 'Niveau 2', " +
"hd_aanvragen.outlook_id" +
"FROM hd_aanvragen " +
"INNER JOIN hd_meldingen ON hd_meldingen.melding_id = hd_aanvragen.melding_id " +
"INNER JOIN hd_melding_niveau_1 ON hd_melding_niveau_1.niveau1_id = hd_meldingen.niveau1_id " +
"INNER JOIN hd_melding_niveau_2 ON hd_melding_niveau_2.niveau2_id = hd_meldingen.niveau2_id " +
"INNER JOIN hd_aanvraag_fase ON hd_aanvraag_fase.aanvraag_id = hd_aanvragen.aanvraag_id " +
"INNER JOIN hd_statussen ON hd_statussen.status_id = hd_aanvraag_fase.status_id " +
"INNER JOIN hd_werknemers AS oplosser ON oplosser.werknemer_Id = hd_aanvraag_fase.werknemer_Id " +
"INNER JOIN hd_werknemers AS aanvrager ON aanvrager.werknemer_Id = hd_aanvragen.werknemer_Id " +
"WHERE hd_statussen.status_id = 16";

But if I run this select-statement here: MyDataSource.SelectCommand = select; I get this error: Incorrect syntax near the keyword 'INNER'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'INNER'.

View 4 Replies

SQL Server :: Incorrect Syntax Near The Keyword "ON"

Dec 14, 2010

i have 3 tables to inner join to get the values. employee_id, employee_name from employee table emplyee_assessment, manager_assessment from Appraisal table. status from objective table

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_selectemployeeapprisaldetails]
@employee_name Varchar(30) =null,
@years Varchar(30) =null,
@designation Varchar(30) =null,
@status Varchar(30) =null
AS
BEGIN

--Set NULL when variable is NULL or length is zero
if @employee_name is not null and len(@employee_name)=0 set @employee_name = null
if @years is not null and len(@years)=0 set @years = null
if @designation is not null and len(@designation)=0 set @designation = null
If @status = 'Select' Set @status = null
select employee.employee_id, employee.employee_name, Appraisal.years, Appraisal.emplyee_assessment, Appraisal.manager_assessment, objective.status
from employee INNER JOIN ON (objective INNER JOIN ON Appraisal ON objective.objective_id = Appraisal.objective_id) ON employee.employeeid = Apprasial.employee_id
WHERE employee.emplyee_id = Appraisal.employee_id and Appraisal.objective_id = objective.objective_id and objective.status = 'Active'
END

but i am getting errors like 1) Incorrect syntax near the keyword 'ON'.

View 2 Replies

SQL Server :: EXEC 17P_Comose Will Give Incorrect Syntax Error - Incorrect Syntax Near '17'

Sep 15, 2010

I am working with a sql server database with about 50 stored procedures. The database and stored procedureswere not created by me. The stored procedures all begin with a number in their name.

Example: 17P_Comsetter

The problem I am having is that if I try and execute the stored procedure at the command line in Query Analyzer,the query analyzer appears not to like that the stored procedure begins with a number. For example:

If I try an run the stored procedure as follows:

EXEC 17P_Comsetter

Then I get the following error message:

Msg 102, ..... Incorrect Syntax near '17'

I am using SQL server 2005. Does anyone know why I am getting this error message?

View 1 Replies

DataSource Controls :: Shopping Cart Error: Incorrect Syntax Near The Keyword

Apr 20, 2010

i am making a shopping cart and i am receiving this error when tryin to add a product to an order. Incorrect syntax near the keyword 'Order'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Order'. Source Error:

[Code]....

Source File: c:UsersPerk-OnDocumentsVisual Studio 2008WebSitesestApp_CodeCart.cs Line: 69 Stack
Trace:

[Code]....

Here is my createorder method in the cart class:

[Code]....

public string CreateOrder (string userid)

View 9 Replies

DataSource Controls :: StoredProc - Msg 156 - Incorrect Syntax Near Keyword "DECLARE"

May 20, 2010

I am getting these errors when compiling => Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'AS' Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'DECLARE'.

SET ANSI_NULLS
ON
SET QUOTED_IDENTIFIER
ON
GO
AS
ALTER PROCEDURE [dbo].[Z_DQA_LOAD_RECIPE]
DECLARE @instbl
varchar(50)
SET @instbl
=
'Z_DQA_'
DECLARE @instbl_def
varchar(50)
SET @instbl_def
=
'Z_DQA_'
DECLARE @updttbl
varchar(20)
DECLARE @Run
int
DECLARE @Defect
int
DECLARE @Test_Num
int
DECLARE @Test_Desc
varchar(50).............................

View 3 Replies

System.Data.SqlClient.SqlException: Incorrect Syntax Near The Keyword "FROM"

Jan 21, 2011

i am trying to create an insertion form in ASP.net.but i get this exception and i could not find this with google.

<div id = "survey_div">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:surveyConnectionString1 %>"
DeleteCommand="DELETE FROM survey"
InsertCommand="INSERT INTO survey(age, country, city) VALUES (,,)"
SelectCommand="SELECT FROM survey" UpdateCommand="UPDATE survey SET">
</asp:SqlDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" CellPadding="4"
DataSourceID="SqlDataSource1" EnableModelValidation="True" ForeColor="#333333"
GridLines="None" Height="50px" Width="125px">
<AlternatingRowStyle BackColor="White" />
<CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />
<EditRowStyle BackColor="#2461BF" />
<FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />
<Fields>
<asp:CommandField ShowInsertButton="True" />
</Fields>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
</asp:DetailsView>
</div>

View 2 Replies

DataSource Controls :: Incorrect Syntax Near The Keyword "order"

Jul 9, 2010

I wrote a select statement such as:

[Code]....

View 4 Replies

DataSource Controls :: Incorrect Syntax Near The Keyword "FROM" Error

Apr 10, 2010

I have a GridView on my web site where column "Tariff Description" I have configured as DropDownList where two values are included. When user updates client details, I want him to be able to assign Tariff from those two values. When I click Edit on user details, change one tariff value to another and click Update, I get "Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FROM'." error message. Following is a code I use:

[Code]....

View 6 Replies

DataSource Controls :: Incorrect Syntax Near The Keyword "AS" - When Try To SELECT Details Of Job And Region

Jul 4, 2010

I am trying to use this code:

[Code]....

And i get this error:

Incorrect syntax near the keyword 'AS'. What i am trying to do is SELECT details of a job and also SELECT which region the job is in. I currently have this: [URL] But i need to change it so it shows the Job name, details and then the regions..

View 7 Replies

DataSource Controls :: Declare Attribute With Select Top 1 / Error Incorrect Syntax Near The Keyword 'select'

Apr 14, 2010

I will declarate a attribute, but it gives me an error

This query works fine and returns 1 record from type int:

SELECT TOP 1 DataObjectVersionID
FROM tblDataObjectVersionPropertyValueText
WHERE PropValue like CAST('00010281' AS ntext)
ORDER BY DataObjectVersionID DESC

And when I will declarate a attribute/parameter it gives me an error:

DECLARE @dataObjectVersionId INT
SET @dataObjectVersionId = SELECT TOP 1 DataObjectVersionID
FROM tblDataObjectVersionPropertyValueText
WHERE PropValue like CAST('00010281' AS ntext)
ORDER BY DataObjectVersionID DESC

Error message:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.

View 4 Replies

DataSource Controls :: Incorrect Syntax Near The Keyword "and"

May 13, 2010

this is my code for binding a gridview on based of two dropdownlists selected choices.

protected void Bind_NBD()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["contest"].ConnectionString);
con.Open();
StringBuilder sub = new StringBuilder("select * from mtblNBD where (1=1)
order by Deadline desc");
SqlCommand com = new SqlCommand(sub.ToString(), con);
if (ddlDivision.SelectedValue != "0")
{
sub.Append(" and Division = @Division");
com.CommandText = sub.ToString();
com.Parameters.AddWithValue("@Division", ddlDivision.SelectedItem.Text);
;
}
if (ddlGroup.SelectedValue != "0")
{
sub.Append(" and GroupIn=@GroupIn");
com.CommandText = sub.ToString();
com.Parameters.AddWithValue("@GroupIn", ddlGroup.SelectedItem.Text);
}
SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;

if in this query StringBuilder sub = new StringBuilder("select * from mtblNBD where (1=1) order by Deadline desc"); I remove the bolded part "order by Deadline desc" then it works good. but with this part the result is with an error. Incorrect syntax near the keyword 'and'. what causes the error.

View 20 Replies

DataSource Controls :: Incorrect Syntax Near The Keyword "IN"

Jul 1, 2010

I get this error: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'IN'. this is my code:

[Code]....

View 8 Replies

DataSource Controls :: Incorrect Syntax Near The Keyword "where"

Oct 13, 2010

When I use the sqldatasource select paremeters like that:

<asp:SqlDataSource ID="dtsrcHotAreaLabor" runat="server"
ConnectionString="<%&#36; ConnectionStrings:ContentMiningConnectionString %>"
SelectCommand="SELECT * FROM [HotAreaLabors] ORDER BY [Date] where [UserAlias]=@UserAlias"
UpdateCommand="UPDATE [HotAreaLabors] set [Date]=@Date,[LaborType]=@LaborType,[Comments]=@Comments where [lid]=@lid and [UserAlias]=@UserAlias"
DeleteCommand="DELETE FROM [HotAreaLabors] WHERE [lid]=@lid and [UserAlias]=@UserAlias"
onselecting="dtsrcHotAreaLabor_Selecting">
<SelectParameters>
<asp:Parameter Type="String" Name="UserAlias"/>
</SelectParameters>
<UpdateParameters>
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="UserAlias" />
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="Date" />
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="LaborType" />
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="Comments" />
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="lid" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="lid" />
<asp:Parameter ConvertEmptyStringToNull="true" Type="String" Name="UserAlias"/>
</DeleteParameters>
</asp:SqlDataSource>

And

protected void dtsrcHotAreaLabor_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["@UserAlias"].Value = Profile.UserName;
}

When I run this page, I got the error "Incorrect syntax near the keyword 'where'."

View 1 Replies

Yield Keyword Is Statement Keyword Or Contextual Keyword?

Mar 15, 2011

Is yield keyword is Statement keyword or Contextual Keyword?

Because in MSDN yield keyword is mentioned in both type of Keywords so i'm little bit confused in this.

View 2 Replies

Web Forms :: "Incorrect Syntax Near The Keyword File" Error When Storing Uploaded File Into Db?

Dec 15, 2010

Actually i am trying to storing the uploaded file into database but when i run this codes:

[Code]....

I got this error: " Incorrect syntax near the keyword 'File'.Incorrect syntax near the keyword 'File'. " can anybody tell me why this error came out?I tried toi change my connection string in web config,but it still showing the same error each time i run it in my local.

View 8 Replies

SQL Server :: Incorrect Syntax Near BlnIsActiveST?

Jul 17, 2010

basically i created a form to suspend or activate user accounts in sql:

UPDATE membermanagement2
SET blnIsActiveST='True'
WHERE intUserIdUS= pintUserID

when i test this form on SQL server 2005 it works!!! but when i bring it over to visual studio... this error always comes out

there are 3 forms involve in this

[code]....

View 3 Replies

SQL Server :: Incorrect Syntax Near 'apply'

Jan 5, 2011

I have a UDF called Split which accepts a comma separated list and returns a table variable splitting the values in a row. So for ex if I call the function

Select * From dbo.Split('100,287,312', ',')

The output of this query will be

ID-----Value
1------100
2------287
3------312

Now I am trying to use cross apply to return each row for each csv but I am getting an error Line 3: Incorrect syntax near 'apply'.

Here is my query

select c.course_id, c.course_title, b.*
from course c cross apply dbo.[Split](c.foreign_lang_equiv_course_id, ',') As b

View 2 Replies

SQL Server :: Incorrect Syntax In Query For Xml

Nov 18, 2010

SELECT *
FROM Customers
WHERE Region is null
FOR XML PATH('Customer'), ROOT('doc')

getting incorrect syntax nr path

View 7 Replies

SQL Server :: Incorrect Syntax ' OFFSET ' ! - How To Correct It

Mar 22, 2011

I have a problem and I'm at an impasse. Incorrect syntax ' OFFSET ' ! - how to correct it

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved