DateTime Error In Stored Procedure
Apr 15, 2004
Hi;
I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??
Create Procedure AddLog
(
@User char(10),
@DLogon DateTime(8),
@Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@User,DLogon,Status)
ERROR:
Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
View 1 Replies
ADVERTISEMENT
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Jul 23, 2005
Hi Everyone,I've been battling this for two days with no luck. I'm using SQLServer 2000.Here's the mystery: I've got a stored procedure that takes a singlevarchar parameter to determine how the result set is sorted. Here itis:CREATE PROCEDURE spDemo @SortField varchar(30)ASSELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDescFROM ActivityLogORDER BY CASE @SortFieldWHEN 'dtmTimeStamp' THEN dtmTimeStampWHEN 'strEmpName' THEN strEmpNameWHEN 'strOld' THEN strOldWHEN 'strNew' THEN strNewWHEN 'strActionDesc' THEN strActionDescENDGOWhen I execute the stored procedure in the Query Analyzer, it worksperfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or'strNew'. When passing in any of the other three possible values for@SortField, I get the following error:Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4Syntax error converting datetime from character string.Now instead of executing the stored procedure, if I copy and paste theSELECT statement directly into the Query Analyzer (after removing theCASE statement and manually trying each different value of @SortField),it works fine for all five possible values of SortField.Even though the error points to Line 4 of the stored procedure, itseems to me that the CASE statement is causing problems for some, butnot all, values of the @SortField parameter.Any ideas?Thanks,Jimmy
View 4 Replies
View Related
Mar 14, 2008
Hi,
Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateActiveStates1]
@PROVIDERID INT
AS
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @ACTIVE BIT
SET @ACTIVE = 0
SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)
SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)
IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))
BEGIN
PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'
SET @ACTIVE=0
END
ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())
BEGIN
IF (@ENDDATE = NULL)
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'
SET @ACTIVE=1
END
ELSE IF (@ENDDATE >= GETDATE())
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'
SET @ACTIVE=1
END
ELSE
BEGIN
PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'
SET @ACTIVE=0
END
END
UPDATE Providers SET Active=@ACTIVE
WHERE ProviderID=@PROVIDERID
UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID
View 2 Replies
View Related
Jan 3, 2008
Hello,
I have got an SQL server stored procedure, and I would like to get this stored procedure, dbo.SpDate_Time_Minute_Today below executed against a matching or exact datetime from the database tables, PRODUCT_SALES.
Code Block
CREATE PROCEDURE dbo.SpDate_Time_Minute_Today
AS
SELECT PROD_SAL_DESC FROM PRODUCT_SALES
WHERE (CONVERT(CHAR(11), PROD_SAL_BY_DATE, 103) + ' ' + SUBSTRING(CONVERT(CHAR(17), PROD_SAL_BY_DATE, 100), 13, 19) = CONVERT(CHAR(11), CURRENT_TIMESTAMP, 103)
+ ' ' + SUBSTRING(CONVERT(CHAR(17), CURRENT_TIMESTAMP, 100), 13, 19))
/* SET NOCOUNT ON */
RETURN
PRODUCT_SALES has columns like this:
PROD_SAL_NO
PROD_SAL_BY_DATE
PROD_SAL_DESC
PROD_SAL_MFR
PROD_SAL_DEPT
Please do anyone here know how I can achieve this please. Thanks.
View 6 Replies
View Related
May 16, 2008
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".
Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();
SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs
if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}
I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View 3 Replies
View Related
Dec 14, 2006
How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies
View Related
May 23, 2007
Hello everyone I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter
cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime));
cmdPublish.Parameters["@datPublishDate"].Value = ????
Thanks
View 3 Replies
View Related
Jan 18, 2008
hello,
I have a stored procedure being called from my class.
All values are ok, except for the DateTime value. What am i doing wrong here ? Am wondering if its the date size of 50 I put there or what ?
command.Parameters.Add(new SqlParameter("@dob", SqlDbType.DateTime,50, dob1));
Error Message
Compiler ErrorMessage: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlParameter.SqlParameter(string, System.Data.SqlDbType, int, string)' has some invalid arguments
thanks
Ehi
View 3 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
Nov 3, 2005
In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)
The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,
However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.
I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.
I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?
Thanks,
Sarah
View 2 Replies
View Related
Sep 9, 2007
Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:
SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition
C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();
Thank you,
View 2 Replies
View Related
Feb 5, 2004
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.
View 5 Replies
View Related
Nov 23, 2005
Hi,When I pass a date time parameter the stored procedure takes about 45seconds, when I hard code the parameter it returns in 1 second. How canI rewrite my stored procedure?@createddatelower datetimeWHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45seconds)vs.WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') ANDdbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)thanks for your help,Paul
View 5 Replies
View Related
Nov 19, 2006
Hi,
I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .
Stored procedure:
ALTER PROCEDURE dbo.SPTest
@testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @testvalue, 'EUR/USD')
RETURN 1
VB.NET code:
Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))
When I run this the ReturnVal is 0.
I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.
View 1 Replies
View Related
Apr 26, 2008
I want to store datetime data in data table.
I am facing problem in using datetime data value in insert statement passed via stored procedure.
Any Help !Thanks in advance
View 3 Replies
View Related
Nov 22, 2007
Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string. Any ideas? ============================== CREATE PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List ( @websiteID AS int = 0, @SelectedDateFrom AS dateTime, @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO============================== <Columns> <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn> <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn> <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn> <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn> <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn> <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn> </Columns> ============================
View 1 Replies
View Related
Nov 1, 2007
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
View 2 Replies
View Related
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Oct 17, 2006
Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks
View 5 Replies
View Related
Jul 29, 2007
hi,
i want to take the first n values from my Categorii table, here is my stored procedure:
SELECT ROW_NUMBER() OVER (ORDER BY CategoryID) AS RowNumber, CategoryID, Name, Description FROM Categorii
WHERE DepartamentID = @DepartamentID AND RowNumber <= 5
i get the error: invalid column name RowNumber
why? what should i do? if i execute the procedure without the AND RowNumber <= 5 i get the RowNumber values 1 to 9 (that means it works)...but what should i do to retrive only the first n?
thank you
View 2 Replies
View Related
Aug 17, 2007
I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks. ALTER procedure [dbo].[USP_Account_Search_Mod]
@ClientCode VARCHAR(7) = ''
,@DebtorName VARCHAR(25) = '',@DebtorNumber INT = 0
,@AccountNumber VARCHAR(30) = ''
,@ReferenceNumber VARCHAR(30) = '',@Tier INT = 0
,@Status VARCHAR(5) = ''
,@UserID INT
,@Month DateTime = NULL
,@FromDate DateTime = NULL
,@ToDate DateTime = NULL,@OriginalMin decimal = 0
,@OriginalMax decimal = 0,@CurrentMin decimal = 0
,@CurrentMax decimal =0
,@lstAmountSelect VARCHAR(3),@IsActive bit = 1
ASDECLARE
@SQLTier1Select VARCHAR(2000)
,@SQLTier2Select VARCHAR(2000)
,@Criteria VARCHAR(2000)
,@SQL VARCHAR(8000)
,@CRI1 VARCHAR(100)
,@CRI2 VARCHAR(100)
,@CRI3 VARCHAR(100)
,@CRI4 VARCHAR(100)
,@CRI5 VARCHAR(100)
,@CRI6 VARCHAR(200)
,@CRI7 VARCHAR(500)
,@CRI8 VARCHAR(500)
,@CRI9 VARCHAR(500)
SELECT @CRI1 = ''
,@CRI2 = ''
,@CRI3 = ''
,@CRI4 = ''
,@CRI5 = ''
,@CRI6 = ''
,@CRI7 = ''
,@CRI8=''
,@CRI9=''
,@Criteria = ''
SELECT @DebtorName = REPLACE(@DebtorName,'''','''''');
Print @DebtorName
if(SELECT UserTypeID FROM dbo.tbl_Security_Users Where UserID = @UserID) = 3 AND @ClientCode = ''
return (-1)IF LEN(@DebtorName) > 0
SET @CRI1 = ' AND Name like ' + '''%' + @DebtorName + '%'''IF @DebtorNumber > 0
SET @CRI2 = ' AND Number = ' + CAST(@DebtorNumber AS VARCHAR(7))IF LEN(@AccountNumber) > 1
SET @CRI3 = ' AND AccountNumber like ' + '''%' + @AccountNumber + '%'''IF LEN(@ReferenceNumber) > 0
SET @CRI4 = ' AND Account like ' + '''%' + @ReferenceNumber + '%'''IF LEN(@ClientCode) > 1
SET @CRI5 = ' AND Customer = ' + '''' + @ClientCode + ''''
SET @Status = RTRIM(@Status)
IF ((@Status Not IN ('ALL','ALA','ALI')) AND (LEN(@Status)>1))
BEGIN
IF(@Status = 'PAID')
SET @CRI6 = ''
IF(@Status = 'CANC')
SET @CRI6 = ' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'
END
--PRINt @CRI6IF LEN(CONVERT(CHAR(8), @Month, 112)) > 0
BEGIN
IF(LEN(CONVERT(CHAR(8), @FromDate, 112)) > 0 AND LEN(CONVERT(CHAR(8), @ToDate, 112)) > 0 )
BEGIN
SET @CRI7 = ' AND Received BETWEEN ' + '''' + CONVERT(CHAR(8), @FromDate, 112)+ '''' + ' AND ' + '''' + CONVERT(CHAR(8), @ToDate, 112) +''''END
ELSEBEGIN SET @CRI7 = ' AND DATEPART(mm, Received) = DATEPART(mm, ' + '''' + CONVERT(CHAR(8), @Month, 112) + '''' + ') AND DATEPART(yy, Received) = DATEPART(yy, ' + '''' + CONVERT(CHAR(8), @Month, 112) + ''''
END END
IF @lstAmountSelect='ALL'
SET @CRI8=''
else IF @lstAmountSelect = 'DR'
BEGIN
SET @CRI8=' AND OriginalBalance >= '+ convert(Varchar,@OriginalMin) + 'AND OriginalBalance<=' + convert(Varchar,@OriginalMax)+' AND CurrentBalance >= '+ convert(Varchar,@CurrentMin) + 'AND CurrentBalance<=' +convert(Varchar,@CurrentMax)
END
ELSE IF @lstAmountSelect = 'OLC'
BEGIN
SET @CRI8=' AND OriginalBalance < CurrentBalance '
END
ELSE IF @lstAmountSelect = 'OGC'
BEGIN
SET @CRI8=' AND OriginalBalance > CurrentBalance '
END
ELSE IF @lstAmountSelect = 'OEC'
BEGIN
SET @CRI8=' AND OriginalBalance = CurrentBalance '
END
SELECT @Criteria = @CRI1 + @CRI2 + @CRI3 + @CRI4 + @CRI5 + @CRI6 + @CRI7 + @CRI8
--PRINT @Criteria
--PRINT @CRI7
if @Status = 'ALL' OR @Status = 'ALA' OR @Status = 'ALI' --All Period
BEGIN
if(@Status = 'ALL') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'
END
if(@Status = 'ALA') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'
END
if(@Status = 'ALI') --All Inactive
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'
ENDEND
ELSE IF @Status = 'PAID'
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'END
ELSE
BEGINSELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria END
SELECT @SQL = CASE @Tier
WHEN 0 THEN @SQLTier1Select + ' UNION ' + @SQLTier2Select + 'ORDER BY NAME ASC' WHEN 1 THEN @SQLTier1Select + 'ORDER BY NAME ASC'
WHEN 2 THEN @SQLTier2Select + 'ORDER BY NAME ASC 'END
PRINT @SQL
--SELECT @SQL
EXEC (@SQL)
View 5 Replies
View Related
Sep 6, 2007
When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near '@SQL'.
Here is my procedure. Could someone tell me what mistake I'm doing.Alter procedure [dbo].[USP_SearchUsersCustomers_New]
@UserID INT
,@RepName VARCHAR(50)
,@dlStatus VARCHAR(5) = ''
as
Declare
@Criteria VARCHAR(500)
,@SQL VARCHAR(8000)
SELECT @Criteria = ''SET NOCOUNT ON
if (@dlStatus <>'ALL' AND (LEN(@dlStatus)>1))
BEGIN
if(@dlStatus='ALA')
SET @Criteria='AND dbo.tbl_Security_Users.IsActive=1'
else
SET @Criteria='AND dbo.tbl_Security_Users.IsActive=0'
END
--If the user is an Admin, select from all users.
if(dbo.UDF_GetUsersRole(@UserID) = 1)
BEGIN@SQL = 'SELECT U.UserID
--,U.RoleID
,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR
INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleIDWhere UR.UserID = U.UserID), 'Unassigned') as 'RoleName'
,U.UserName
,U.Name
,U.Email
,U.IsActive
,U.Phone
FROM dbo.tbl_Security_Users U
--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID
WHERE U.NAME LIKE @RepName AND U.UserTypeID < 3'+ @Criteria
END
View 6 Replies
View Related
Jan 29, 2008
Hi,I want to use a variable to put a value in a table but it doesn't seems to works. How can i do that? I have bolded and underlined the text that i think is not correct.What syntax can i use to make it work?Thanks----------------------------------------------------------------------dbo._UpdateImage(@ID int,@ImageID int)
ASBegin
Declare @PhotosThumb nvarchar(50)Declare @Photos nvarchar(50)
SET @PhotosThumb = 'PhotosThumb' + convert(nvarchar, @ImageID)SET @Photos = 'Photos' + convert(nvarchar, @ImageID)
SET NOCOUNT ON
IF @ImageID = 1
UPDATE PhotosSET @PhotosThumb = 'Logo_thumb.gif',@Photos = 'Logo320x240.gif'WHERE ID = @ID
ELSE
UPDATE PhotosSET @PhotosThumb = NULL,@Photos = NULLWHERE ID = @ID
SET NOCOUNT OFFEND
View 6 Replies
View Related
Feb 12, 2008
Yo people, got a little problem with this stored procedure, i go to save it and it kicks out these errors:
Incorrect syntax near the keyword 'Drop'.Incorrect syntax near 'Go'.Incorrect syntax near 'Go'.'CREATE/ALTER PROCEDURE' must be the first statement in the query batch
I dont no about this sort of stuff so a good break down of what wrong would be good, below is the whole procedure.
CREATE PROCEDURE dbo.SQLDataSource1
Drop Table PersonGo
Create Table Person
(PersonID Int Identity,
PersonEmail Varchar(255),PersonName Varchar(255),
PersonSex Char(1),PersonDOB DateTime,
PersonImage Image,PersonImageType Varchar(255)
)
Drop Proc sp_person_isp
Go
Create Proc sp_person_isp
@PersonEmail Varchar(255),@PersonName Varchar(255),
@PersonSex Char(1),@PersonDOB DateTime,
@PersonImage Image,
@PersonImageType Varchar(255)
As
BeginInsert into Person
(PersonEmail, PersonName, PersonSex,
PersonDOB, PersonImage, PersonImageType)
Values
(@PersonEmail, @PersonName, @PersonSex,
@PersonDOB, @PersonImage, @PersonImageType)
End
Go
View 4 Replies
View Related
Nov 4, 2003
I have the following code in my code behind page:
Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim CM As New SqlCommand("spCCF_CrossTab", CN)
CM.CommandType = CommandType.StoredProcedure
CM.Parameters.Add(New SqlParameter("@LocationID", "CCFIF"))
CM.Parameters.Add(New SqlParameter("@BeginDate", dtbStart.Text))
CM.Parameters.Add(New SqlParameter("@EndDate", dtbEnd.Text))
CN.Open()
DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
dgReport.DataSource = DR
dgReport.DataBind()
A SQL exception is thrown: Incorrect syntax near the keyword 'END'
But I turned on tracing in Enterprise Manager, the following request is sent to SQL:
exec spCCF_CrossTab @LocationID = N'CCFIF', @BeginDate = N'11/3/2003', @EndDate = N'11/4/2003'
In query analyzer the above line executes without error and returns the expected information.
My stored procedure is:
CREATE PROCEDURE spCCF_CrossTab
@LocationID varchar(10),
@BeginDate varchar(10),
@EndDate varchar(10)
AS
declare @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100), @where varchar(1000)
select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action'
select @sumfunc= 'COUNT(ActionID)'
select @pivot='UserName'
select @table= 'UserInfo'
select @where='(dbo.UserInfo.LocationID = ''' + @LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
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 and ' + @where)
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
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
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
GO
I've been banging my head on this for quite some time now, any insight someone might have as to the problem would be greatly appreciated! Thanks!
View 19 Replies
View Related
May 27, 2004
I am trying to get a returned value from the stored procedure below
CREATE PROC insert_and_return_id
(
@parameter1 varchar,
@parameter2 varchar
)
AS
DECLARE @newID int
SELECT @newID = 0
INSERT INTO tbltest (field1, field2)
VALUES (@parameter1, @parameter2)
IF(@@ROWCOUNT > 0)
BEGIN
SELECT @newID = @@IDENTITY
END
RETURN @newID
GO
___________________________
My asp Code looks like this
___________________________
Function InserTest(value1, value2)
Dim objConn, objRs, objCmd
' Create a connection to the database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "DSN=" & CONNECTION_STRING
' Create the query command
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "insert_and_return_id"
objCmd.CommandType = adCmdStoredProc
' Create the parameter for output and returned valueand populate it
objCmd.Parameters.Append objCmd.CreateParameter("parameter1", adVarChar, adParamInput, 255, value1)
objCmd.Parameters.Append objCmd.CreateParameter("parameter2", adVarChar, adParamInput, 255, value2)
objCmd.Parameters.Append objCmd.CreateParameter("newID", adInteger, adParamReturnValue, 4)
objCmd.Execute objCmd0
response.write objCmd.Parameters("newID")
'objCmd.Close
End Function
And I get the following ASP Error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function insert_and_return_id has too many arguments specified.
/netwasp/tester.asp, line 62
I only just started to use sp's hence it might be something really simple, Can anyone help, cheers?
View 6 Replies
View Related
May 19, 2005
I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ?????
Thanks friends..
Procedure 'UpdateRowsReorderUp' expects parameter '@nextlowestsortID', which was not supplied.
CREATE PROCEDURE [dbo].[UpdateRowsReorderUp]
(
@intsortID int,
@nextlowestsortID int,
@MemberID int
)
AS
Select @nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@intsortID order by SortID DESC)
UPDATE SelectedCredits SET SortID= CASE
WHEN SortID = @nextlowestsortID then @intsortID
WHEN SortID = @intsortID then @nextlowestsortID ELSE SortID End
WHERE MemberID = @MemberID
SELECT * FROM SelectedCredits WHERE MemberID= @MemberID ORDER BY SortID
GO
**************
// this is my script on the page
void moveup(Object s, DataListCommandEventArgs e) {
objcmd= new SqlCommand("UpdateRowsReorderUp",objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@intsortID",intsortID);
objcmd.Parameters.Add("@MemberID",Session["MemberID"]);
objRdr= objcmd.ExecuteReader();
dlSelCredits.DataSource = objRdr;
dlSelCredits.DataBind();
objRdr.Close();
objConn.Close();
BindData();
}
View 4 Replies
View Related
Sep 12, 2005
I have a stored procedure to which I pass the following parameters @Date smalldatetime, @Amount decimal(15,3) @Exg_Rate decimal(5,3)Inside this stored procedure I call another one passing to it those parameters like thatEXECUTE dbo.[Opening_Balance_AfterInsert] @Date, @Amount*@Exg_RateI receive an error at the above line saying: Incorrect syntax near '*'if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('How can I pass that expression?
View 1 Replies
View Related
Nov 26, 2005
String[1]: the Size property has an invalid size of 0.I am using a Stored Procedure, shown hereCREATE PROCEDURE GetImagePath( @ID INT, @ImagePath VARCHAR(50) OUTPUT) AS SET NOCOUNT ONSELECT @ImagePath = path FROM dbo.docs WHERE table1= @IDRETURNGOHere is the asp.net code that I am usingSqlConnection conn = new SqlConnection(strConnection);conn.Open();SqlParameter param = new SqlParameter("@ImagePath", SqlDbType.VarChar);param.Direction = ParameterDirection.Output;SqlCommand cmd = new SqlCommand("GetImagePath", conn);cmd.Parameters.AddWithValue("@ID", 100);cmd.Parameters.Add(param);cmd.CommandType = CommandType.StoredProcedure;cmd.ExecuteNonQuery();cmd.Dispose();conn.Dispose();TextBox1.Text = param.Value.ToString();When I run it, I get the following error String[1]: the Size property has an invalid size of 0. The stored procedure is correct because I tested it wtih Query Analyzer. I cant seem to figure out what is causing this error.Any help would be appreciated.
View 1 Replies
View Related
Mar 24, 2006
I am using SQL Server 2005 and wrote a stored procedure as shown below.CREATE PROCEDURE [dbo].[GetUsers]( @StartRowIndex INT, @MaximumRows INT, @SortExpression VARCHAR(50))ASBEGIN DECLARE @SQL VARCHAR(1000); DECLARE @Start VARCHAR(10); SET @Start = CONVERT(VARCHAR(10), @StartRowIndex + 1); DECLARE @End VARCHAR(10); SET @End = CONVERT(VARCHAR(10), @StartRowIndex + @MaximumRows); SET @SQL = ' WITH Data AS( SELECT UserID, Username, FirstName, LastName, ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users) SELECT UserID, Username, FirstName, LastName FROM Data WHERE RowNumber BETWEEN ' + @Start + ' AND ' + @End EXEC(@SQL);ENDIn VS 2005, I am using a DataSet. Then I created an ObjectDataSource which binds the GridView control. However, I am getting the following error: Incorrect syntax near ')'After playing around with it, the error is from the line:ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users)If I change the line to ROW_NUMBER() OVER(ORDER BY UserID') AS RowNumber FROM Users) it works fine, except for sorting.I dont understand what I might be doing wrong.
View 1 Replies
View Related
Mar 4, 2003
I am writing a stored procedure where I am adding a column to a table, doing some manipulation using the new column and then deleting the column. The problem is when I try to save the stored procedure, it gives me an error because it cannot find the new field on the table. How can I tell SQL to not compile a section or whole stored procedure?
Thanks,
Ken Nicholson
Sara Lee Corporation
View 1 Replies
View Related
