Error 156: Incorrect Syntax Near The Keyword WHEN.
Jan 16, 2008
Hi i have the following code in my sql database;
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN
CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID,0)
WHEN 4 THEN
dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate (IsNull(tblRateSchedule.WorkType_ID,0) ,tblWorkSchedule.WorkSchedule_ID , tblSurvey.PropertyYear_ID , tblSurvey.PropertyPeriod_ID ) END
I want to add the following lines to it, however it gives me an error "Error 156: Incorrect syntax near the keyword "WHEN". What do i need to do, thank you.
WHEN 3 THEN
dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate (IsNull(tblRateSchedule.WorkType_ID,0) ,tblWorkSchedule.WorkSchedule_ID , tblSurvey.PropertyYear_ID , tblSurvey.PropertyPeriod_ID ) END
Hi, I am building a website and database system for a university project, and am getting an error message when inserting data to a table (error message = "System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'."). I would be most grateful if anybody could point out where I am going wrong! My code is; using System; using System.Collections; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; public partial class Default2 : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {if ((Session["sUserName"]) == null) {Response.Redirect("Default.aspx"); } else {Convert.ToString(Session["sUserName"]); } }protected void Register(object sender, EventArgs e) { //Create the ConnectionSqlConnection sqlConn = new SqlConnection("server=Acer-Laptop\SQLEXPRESS; database=NeuCar; Trusted_Connection=true"); //Open the connection sqlConn.Open(); //Create the Command, passing in the SQL statement and the ConnectionString queryString = "INSERT INTO Member (FirstName, LastName, Title, Email, AddressLine1, AddressLine2, TownOrCity, County, Postcode) VALUES(@myFirstName, @myLastName, @myTitle, @myEmail, @myAddressLine1, @myAddressLine2, @myTownOrCity, @myCounty, @myPostcode) WHERE Member (UserName = @myUserName); "; SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUserName", Session["sUserName"])); cmd.Parameters.Add(new SqlParameter("@myFirstName", FirstNameTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myLastName", LastNameTextBox.Text)); cmd.Parameters.Add(new SqlParameter("@myTitle", TitleTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myEmail", ConfirmEmailTextBox.Text)); cmd.Parameters.Add(new SqlParameter("@myAddressLine1", AddressLine1TextBox.Text));cmd.Parameters.Add(new SqlParameter("@myAddressLine2", AddressLine2TextBox.Text)); cmd.Parameters.Add(new SqlParameter("@myTownOrCity", TownOrCityTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myCounty", CountyTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myPostcode", PostcodeTextBox.Text)); cmd.ExecuteNonQuery(); Response.Redirect("Register.aspx"); //Close the connection sqlConn.Close(); } } Also, is this line of code correct for passing a session variable into a parameter?; cmd.Parameters.Add(new SqlParameter("@myUserName", Session["sUserName"])); Many thanks for your time and help! Chima
Following is the stored procedure iam trying to create.Here i am trying to
First create a table with the table name passed as parameter Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows. Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.
This table would be further used with cursor. Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.
Could someone please help me!!! I am using a multi-value parameter in SQL 2005 reports and am getting the following error message:
An error has occured during report processing. Qiery execution failed for dataset an expression of non-boolean type specified in a context where condition is expected, near ',' Incorrect syntax near keyword else.
The multi-value parameter works when it isn't run in the if, else clause i checked the where clause with a single paramter and it works OK. I don't understand what is causing this problem but I really need to fix it. Here is my query.
if @job_SubRep_ProjNo_param = '0'
Begin select Job_Job_No as job, Job_Job_Name as Job_title, cast(Job_Total_Fee as float) as fee, employee_first_name + ' ' + employee_surname as jl_name, cast(Job_Percent_Complete as float) as percentcomplete, cast(Job_Work_Done as float) as workdone, cast(Job_Invoicing as float) as job_Invoicing, job_WIP, Job_Expenditure as timecost, job_project_no, Job_Profit_Loss, cast(Job_Hours as float) as hours, job_expenses from job_tbl inner join project_tbl on job_project_no = project_no inner join employee_tbl on job_jl_empno = employee_no where project_pl_empno = @pl_employeeNo or job_jl_empno = @jl_subRep
end else select Job_Job_No as job, Job_Job_Name as Job_title, cast(Job_Total_Fee as float) as fee, employee_first_name + ' ' + employee_surname as jl_name, cast(Job_Percent_Complete as float) as percentcomplete, cast(Job_Work_Done as float) as workdone, cast(Job_Invoicing as float) as job_Invoicing, job_WIP, Job_Expenditure as timecost, job_project_no, Job_Profit_Loss, cast(Job_Hours as float) as hours, job_expenses from job_tbl inner join project_tbl on job_project_no = project_no inner join employee_tbl on job_jl_empno = employee_no where job_project_no in (@job_SubRep_ProjNo_param) or job_jl_empno = @jl_subRep
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT 2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end 3 FROM abc 4 Anyone can help? Thanks a lot.
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _ & "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0")) buildDD(sql, ddlPerNames)
Hi,I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:Incorrect syntax near the keyword 'ELSE'.I am using Sql Server 2005.SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate]( @ReportDate smalldatetime, @SiteID int, @DataFeedID int, @FromCode varchar, @Sent int, @Delivered int, @TotalOpens REAL, @UniqueUserOpens REAL, @UniqueUserMessageClicks REAL, @Unsubscribes REAL, @Bounces REAL, @UniqueUserLinkClicks REAL, @TotalLinkClicks REAL, @SpamComplaints int, @Cost int)ASDECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCode SELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDENDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost FROM CrmPublisherSummary WHERE @LastReportDate < @ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET Sent=@Sent, Delivered=@Delivered, TotalOpens=@TotalOpens, UniqueUserOpens=@UniqueUserOpens, UniqueUserMessageClicks=@UniqueUserMessageClicks, UniqueUserLinkClicks=@UniqueUserLinkClicks, TotalLinkClicks=@TotalLinkClicks, Unsubscribes=@Unsubscribes, Bounces=@Bounces, SpamComplaints=@SpamComplaints, Cost=@Cost WHERE ReportDate=@ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDELSE SET NOCOUNT ON INSERT INTO CrmPublisherSummary( ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes, Bounces, SpamComplaints, Cost, DataFeedID, TagID) SELECT @ReportDate, @SiteID, @PKID, @Sent, @Delivered, @TotalOpens, @UniqueUserOpens, @UniqueUserMessageClicks, @UniqueUserLinkClicks, @TotalLinkClicks, @Unsubscribes, @Bounces, @SpamComplaints, @Cost, @DataFeedID, @TagIDSET NOCOUNT OFF
HiI am getting the following error on my Select statement: Incorrect syntax near the keyword 'AS'. SELECT [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], RowFROM(SELECT ROW_NUMBER() OVER (ORDER BY [J] DESC)AS Row, [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K]FROM [TABLE]WHERE (([A] LIKE '%' + @A+ '%') OR ([K] LIKE '%' + @K+ '%')) AS LogWithRowNumbersWHERE (Row >=91 AND Row <= 100) I used the following select statement as a template (which works fine): SELECT [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], [L], Row
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [H] DESC) AS Row, [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], [L] FROM [TABLE]
WHERE (([J] = @J) AND ([E] >= @E)) AND (([K] < [L]) OR (([K] = 0) AND ([L] = 0)))) AS LogWithRowNumbers WHERE (Row >= 82 AND Row <= 90) What is the difference that would make one work and the other not work?Thanks if you can help,Jon
Hi,I was just wondering if some kind soul on here could help me. I created a table in a database with called 'EuropeBroadcastList', with the following columns: Name, First Name, First, Last Name, Last, Title, Company, Photo.I have created a query whereby I want to create a new table with only specific columns called 'EuropeBroadcastSorted' and sort the names in 'EuropeBroadcastList' by the 'Last' column. The query is: CREATE TABLE europebroadcastsorted AS (SELECT [name], title, company, photo, [Last] FROM EuropeBroadcastList ORDER BY Last ASC); When I execute it I get the following syntax errors: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'ORDER'.Can any kind person help me resolve this?Thanks Rob
Can someone help me? I am trying to script an SQL statement that would allow someone to INSERT a new username into a database where it is not a duplicate entry. The table is like this: UserID - int, 4, identity(1,1) UserName - nvarchar(50) UserPass - nvarchar(50)
The code to execute this where i am getting the errors is this:
Function ChooseUName() If Page.IsValid Then Dim objCon As New SqlConnection(con) Dim sqlInsert As String = "INSERT INTO tblUser (UserName) " & _ "VALUES (@Username) WHERE NOT EXISTS (SELECT UserName FROM tblUser)" Dim cmd As New SqlCommand(sqlInsert, objCon) cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50) cmd.Parameters("@Username").Value = txtUsername.Text
Dim id As Integer Try objCon.Open() id = cmd.ExecuteScalar() Finally If objCon.State = ConnectionState.Open Then objCon.Close() End If End Try
Response.Write("Your User ID is: " & id.ToString()) Response.End() End If End Function
This is the error:
Incorrect syntax near the keyword 'WHERE'.
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 'WHERE'.
Source Error:
Line 73: Try Line 74: objCon.Open() Line 75: id = cmd.ExecuteScalar() Line 76: Finally Line 77: If objCon.State = ConnectionState.Open Then
PLEASE HELP!! I'M ON A STRICT DEADLINE!!! :o THANKS IN ADVANCE!
This one is driving me nuts... I have been getting this error in my Asp.Net page. I run the profiler and then I can grab the actual query, and when I run this query in query analizer, it works just fine...
I have no clue at all about what's going wrong with my code or query...
cmd.CommandText = "exec mydatabase.dbo.mytable @select = 'Select convert(varchar(20),J.Datecreated,107)as Date, max(J.datecreated)As DateOrd from table1 J Left Join table2 C on J.CustID = C.CustID where C.Mode = 1 group by convert(varchar(20),J.DateCreated ,107) order by max(J.datecreated) DESC', @sumfunc = 'count(ID)', @pivot = 'Staff', @table = 'table1'"
-========================= STORED Procedure
CREATE PROC myTable ( @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100) ) AS DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '' END FROM tempdb.information_schema.columns WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot DROP TABLE ##pivot
Hi i want to copy tables from sql to accessi gave SELECT Persons.* INTO Persons IN 'Backup.mdb' FROM Persons The error is Incorrect syntax near the keyword 'in'any one reply me
Hi, I have Error when I write this SQL statement retrieving the non selected facilitator for a specific course: The SQL statement:
SELECT facilitators.facilitator_id, facilitators.facilitator_name FROM facilitators INNER JOIN [transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id WHERE (NOT (facilitators.facilitator_id IN (SELECT [transaction_ facilitators].trans_Facilitator_id FROM [transaction_ facilitators] WHERE [transaction_ facilitators].trans_Course_id = 2))) GROUP BY facilitators.facilitator_id, facilitators.facilitator_name ORDER BY facilitators.facilitator_id
Every thing goes right until I change this: WHERE [transaction_ facilitators].trans_Course_id = 2 To WHERE [transaction_ facilitators].trans_Course_id = @course_id
Hi..am getting this error "Incorrect syntax near the keyword 'inner'. Can anyone please get me the correct query.. My Query is:
Code:
SELECT iCalls_Calls.CALL_ID,C.CALL_ID, iCalls_Calls.REQUESTOR, iCalls_Users.USER_NAME, DESCRIPTION, TYPE, SCOPE, SEVERITY, CAST(E.COUNT1/F.COUNT2 AS VarChar(5)), iCalls_Calls.STATUS_ID, iCalls_Status.STATUS_ID, iCalls_Status.STATUS_LABEL FROM iCalls_Calls C INNER JOIN(SELECT CALL_ID,COUNT(CALL_ID)AS COUNT1 FROM iCalls_Events GROUP BY CALL_ID) E ON C.CALL_ID=E.CALL_ID INNER JOIN(SELECT CALL_ID,COUNT(CALL_ID)AS COUNT2 FROM iCalls_Events WHERE EVENTS_FLAG <> 0 GROUP BY CALL_ID ) F ON C.CALL_ID=F.CALL_ID ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.STATUS_ID=iCalls_Status.STATUS_ID) inner Join iCalls_Users on iCalls_Calls.REQUESTOR=iCalls_Users.USER_ID) left outer join iCalls_Messages on iCalls_Calls.CALL_ID=iCalls_Messages.CALL_ID WHERE REQUESTOR='" & Session("USER_ID") & "' AND iCalls_Calls.STATUS_ID <> 6 ORDER BY iCalls_Calls.CALL_ID
I keep getting erroe.. incorrect syntax near the keyword 'where'
"SELECT ISNULL(LastName, '') + SPACE(1) + ISNULL(FirstName, '') AS FullName,Grade,ClassID FROM studentsInfo,StudentClassDetails Where StudentsInfo.StudentID = StudentClassDetails.StudentID And FullName='" & aName & "' And where ClassID='" & clsID & "' group by fullname"
I'm trying to add a sub-query to my initial query, and I continue to receive the error Incorrect Syntax error. I've tried rearranging my join placements, but I continue to receive the error.
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C select distinct T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, s.RES_NAME_SBB, hs.ADDR1_HSE, hs.RES_CITY_HSE, hs.RES_STATE_HSE,
If (@Position1 <> '') THEN SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE seekers.position=jobs.position AND seekers.userid=@userid GO ElseIf (@Position1 <> '') AND (@Position2 <> '') THEN SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE (details.discipline2=jobs.position AND details.userid=@userid) OR (seekers.position=jobs.position AND seekers.userid=@userid) GO ElseIf (@Position1 <> '') AND (@Position2 <> '') AND (@Position3 <> '') THEN SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE ((details.discipline2=jobs.position OR details.discipline3=jobs.position) AND details.userid=@userid) OR (seekers.position=jobs.position AND seekers.userid=@userid) GO END IF END TRAN
Hi Everyone,I really tried to not post this question but I gave up. I tried brackets,parenth...etc but nothing worked. I get this error message: Incorrect syntaxnear the keyword 'THEN'. Please help, I am learning SQL Server.thanks in advance.Ismailuse misselect CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET, CLAIM_HMASTERS_VS.CLAIMNO,'AMOUNT' =CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSECLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)END,FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO =CLAIM_DETAILS_HCVW.CLAIMNOwhere CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200608/1
Any Ideas as to this error message. I am trying to learn using ms sqlserver 7.0Below is the code I am using for an update to a MS Sql Database.<%@ Language=VBScript %><% Option Explicit %><html><head><title>Sample Script 2 - Part 3 </title><!-- copyright MDFernandez ---><link rel="stylesheet" type="text/css" href="../part3sol/style.css"></head><body bgcolor="#FFFFFF"><!--#include virtual="/adovbs.inc"--><center><%Dim oRSDim ConnDim IdDim NameDim StreetAddressDim CityDim StateDim ZipDim PhoneNumberdim sqlId = request.form("Id")Name = request.form("Name")StreetAddress = request.form("StreetAddress")City = request.form("City")State = request.form("State")Zip = request.form("Zip")PhoneNumber = request.form("PhoneNumber")Set Conn = Server.CreateObject("ADODB.Connection")Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunnerProfessionalApplication;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")'Conn.Opensql="update FPFriends"sql=sql & " set Name='" & Name & "',"sql=sql & "StreetAddress='" & StreetAddress & "',"sql=sql & "Ciy='" & City & "',"sql=sql & "State='" & State & "',"sql=sql & "Zip='" & Zip & "',"sql=sql & "PhoneNumber='" & PhoneNumber & "',"sql=sql & " WHERE Id=" & Idset oRS=Conn.Execute (sql)response.write "<font face='arial' size=4>"response.write "<br><br>The record has been updated."response.write "</b></font>"' close the connection to the databaseConn.Close%><!-- don't include in sample code display ---><form><input type="button" value=" Close This Window "onClick="window.location='aboutus.htm'"><br><button onClick="window.location='menu1_1.asp'">Update anotherrecord</button></form></center></body></html>
Hello, I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50 Incorrect syntax near the keyword 'WHERE'.
Code Snippet
ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details] -- Add the parameters for the stored procedure here @inSearchBy varchar(20), @inSearchFor varchar(100)
AS
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @SQL varchar(500) if (@inSearchBy='Email' or @inSearchBy='HomePhone') begin set @SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @inSearchBy + ' = ''' + @inSearchFor + '''' exec ( @SQL ) end else begin SELECT dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName FROM dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName GROUP BY dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName WHERE dbo.aspnet_Users.UserName = ''+@inSearchFor+'' end END My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!
Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"--------------------------------------------------- SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard, Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hitsFROM Videos LEFT OUTER JOIN (SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1 FROM Hits GROUP BY VideoId, UserId) AS TB1 GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoIdORDER BY TB2.hits DESC) AS T1WHERE rownum <= 5----------------------------------------- If I run the query that is in BOLD as: SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above? Thank in advance,Louis
HiI've got a query which works fine in query analzyer but when I put it into my c# it shows the above eror messageCan you not use the following in c# sql string? " FROM table LEFT OUTER JOIN" +thanks! We are not a Code Charity
The above error message shows with the following T-SQL code of a dynamic query: declare CheckColumn cursor for exec('select distinct ' + @myColumnName + ' from ' + @myTableName)The 2 varchar variables are properly declared. What's wrong syntactically?Thanks.
alter PROCEDURE Select_ShortInitials ( @intRetVal int=null
) as begin if (@intRetVal=1) begin
begin Transaction declare @PresentEmp as varchar(50) declare @PEmp as varchar(50) declare @strInsertStatment as varchar(8000) set @strInsertStatment='' declare InsertStatmentSupportedPhonesCur cursor global scroll dynamic optimistic for select name from smartjot.dbo.sysobjects where name like 'Employee_%' and name not like 'Employee_details%' create table #tempShort(Short varchar(50)) open InsertStatmentSupportedPhonesCur fetch first from InsertStatmentSupportedPhonesCur into @PresentEmp while @@fetch_status = 0 begin
declare InnerCursor cursor global scroll dynamic optimistic for EXEC ('select distinct ShortInitial from ' + @PresentEmp)
--select distinct ShortInitial from @PresentEmp
open InnerCursor fetch first from InnerCursor into @PEmp while @@fetch_status = 0 begin
insert into #tempShort(Short) values(@PEmp) fetch next from InnerCursor into @PEmp
end close InnerCursor deallocate InnerCursor
fetch next from InsertStatmentSupportedPhonesCur into @PresentEmp end close InsertStatmentSupportedPhonesCur deallocate InsertStatmentSupportedPhonesCur
--Preparing Insert Statment for SupportedPhones Table - End
Dear Group,I am trying to create a view and keep getting the Incorrect syntax near thekeyword 'Declare'" error.Here is the code I am writing.Create view fixed_airs (sid, fad_a2, fad_a3) asDeclare @sid int,@fad_a2 int,@fad_a3 intselect @sid=cast(substring(subject_id,1,8)as int) ,@fad_a2 =cast (substring(fad_2_4,1,1) as int),@fad_a3=cast(substring(fad_2_4,2,1) as int)from parentpacket.Thanks for the help in advance.Jeff Magouirk
