Why Does The Stored Procedure Display Error?
Feb 16, 2006
Why does the Stored Procedure display error?
I define a Stored Procedure Named Wish_DisplayTitleOfThing7, why does the Stored Procedure display error? Thanks!
Msg 156, Level 15, State 1, Procedure Wish_DisplayTitleOfThing7, Line 9
Incorrect syntax near the keyword 'case'.
create procedure Wish_DisplayTitleOfThing7
@UserName varchar(80),
@Status int,
@Tag varchar(80)=null,
@CreateDate datetime=null
AS
select * from Th_TopicTable where (UserName=@UserName)
case @Status
when 0 then '' --All
when 1 then ' And (CompleteDate is not null )' --Completed
when 2 then ' And (CompleteDate is null )' --Uncompleted
end
case @CreateDate
when null then ''
else ' And (DatePart(year,@CreateDate)=DatePart(year,CreateDate))'
end
View 11 Replies
ADVERTISEMENT
Jan 29, 2008
Could you tell me how to display the content of a MS SQL 2005 stored procedure with C#?
Thanks!
View 6 Replies
View Related
Jul 29, 2004
I want to display the data in datagrid using the stored procedure,
Can you please tell me, how i can create the stored procedure for the following:
using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)
I want to display the stored procedure data in my Datagrid.
Thank you very much for the help.
the following is complete inline code on my webform.
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
Dim objCmd As New SqlCommand
Dim dataAdapter As SqlDataAdapter
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
objConn.Open()
dataAdapter = New SqlDataAdapter
dataAdapter.TableMappings.Add("Table", "Orders")
dataAdapter.SelectCommand = objCmd
dataSet = New DataSet("Orders")
dataAdapter.Fill(dataSet)
dtgOrders.DataSource = dataSet
dtgOrders.DataBind()
objConn.Dispose()
View 1 Replies
View Related
Dec 20, 2006
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
View 1 Replies
View Related
Feb 9, 2006
Hi Guys,
I have a sql procedure that returns the following result when I execute it in query builder:
CountE ProjStatus
6 In Progress
3 Complete
4 On Hold
The stored procedure is as follow:
SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus
This is the result I want but when I try to output the result on my asp.net page I get the following error:
DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.
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.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn>
Line 272: <asp:TemplateColumn>
Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
Line 274: </asp:TemplateColumn>
Line 275: </columns>
My asp.net page is as follows:
<script runat="server">
Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC
myCommandPS.CommandType = CommandType.StoredProcedure
Dim num as integer
num = CInt(myCommand.ExecuteScalar)
'Set the datagrid's datasource to the DataSet and databind
Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)
dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()
myConnection.Close()
</script>
<asp:datagrid id="dgProjSumm" runat="server"
BorderWidth="0"
Cellpadding="4"
Cellspacing="0"
Width="100%"
Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"
Font-Size="xx-small"
AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" >
<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
Please help if you can Im havin real trouble here.
Cheers
View 3 Replies
View Related
Jul 20, 2005
Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!
View 3 Replies
View Related
Mar 7, 2008
Here is the Stored procedure
ALTER procedure [dbo].[ActAuditInfo](@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) output
)asbegindeclare @AuidtID as varchar(30)Select @IndustryName=Industry_Name from Industry whereIndustry.Ind_Id_PK =(Select Audit_Industry from Audits whereAd_ID_PK=@AuidtID)Select @CompanyName=Company_Name from Company whereCompany.Cmp_ID_PK =(Select Audit_Company from Audits whereAd_ID_PK=@AuidtID)Select @PlantName=Plant_Name from Plant where Plant.Pl_ID_PK=(Select Audit_Plant from Audits where Ad_ID_PK=@AuidtID)Select @GroupName=Groups_Name from Groups whereGroups.G_ID_PK =(Select Audit_Group from Audits whereAd_ID_PK=@AuidtID)Select @UserName=Login_Uname from RegistrationDetails whereRegistrationDetails.UID_PK =(Select Audit_Created_By fromAudits where Ad_ID_PK=@AuidtID)SELECT Ad_ID_PK, Audit_Name, @IndustryName, @CompanyName, @PlantName,@GroupName, Audit_Started_On, Audit_Scheduledto, @UserName FROMAudits where Audit_Status='Active'end
U can see here different parameters,my requirement is that iam havingID's of Industry,company,plant,group,username stored in a table calledPcra_Audits and i must display their related names in the front end.so this is the query iam using for that.
Data in the database:Commercial83312 2 2 2 1 1 InactiveHere u can see 2,2,2,1,1 these are the IDs ofindustry,company,plant,group and username and Commercial83312 is tehaudit ID.now i want to display this data in teh front end as i cannot displaythe IDs i am retrieving the names of the particular IDs from therelated tables.Like iam getting name of the IndustryID from Industry Table,in thesame way others too.when iam running this procedure iam getting the gridview blank.iam passing the output parameters:@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) outputinto the function in the frontend and iam calling that into the pageload method.please help me with this.
View 1 Replies
View Related
Jul 20, 2004
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
View 2 Replies
View Related
Mar 16, 2014
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
View 2 Replies
View Related
Jan 31, 2008
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
ByVal strCompany As String, _
ByVal decSerial As Decimal, _
ByVal strSerialCode As String, _
ByVal strSerialScan As String, _
ByVal decMasterSerialNumber As Decimal, _
ByVal strCustomerPart As String, _
ByVal strTakataPart As String, _
ByVal strCustomerRanNo As String, _
ByVal strCustomerAbv As String, _
ByVal strDestinationAbv As String, _
ByVal decQty As Decimal, _
ByVal strCreatDate As String, _
ByVal decVoidSerialNo As Decimal, _
ByVal strProductionLineNo As String, _
ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe
Dim strResult As Integer = 0
Dim strErrorText As String = String.Empty
Dim dsData As New DataSet
Dim parameter(15) As OleDbParameter
If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
' Populate parameter collection
parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))
RunDB2Sp("FABLE.MAP", parameter, dsData)
If dsData.Tables.Count > 0 Then
dsData.Tables(0).TableName = "Supreeth"
Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here
SqlContext.Pipe.Send(bitresult)
SqlContext.Pipe.Send("No errors")
End If
Else
Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
'*********************************************
' Declare Variables
'*********************************************
Dim daAdaptor As OleDbDataAdapter
Dim cmdAS400 As OleDbCommand
'Dim dstestMe As New DataSet
Try
cmdAS400 = CreateCommand(strProcedure, parms)
daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set
daAdaptor.Fill(dsData)
Catch expError As OleDbException
daAdaptor = Nothing
Finally
daAdaptor = Nothing
cmdAS400.Dispose()
'Me.Close()
End Try
End Sub
Public Shared Function CreateParameter(ByVal name As String, _
ByVal type As OleDbType, _
ByVal size As Integer, _
ByVal direction As ParameterDirection, _
ByVal paramValue As Object) As OleDbParameter
Dim param As OleDbParameter = New OleDbParameter
param.ParameterName = name
param.OleDbType = type
param.Size = size
param.Direction = direction
param.Value = paramValue
Return param
End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
Dim CmdSAS400 As OleDbCommand
Dim parameter As OleDbParameter
Dim connAS400 As OleDbConnection
connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
connAS400.Open()
CmdSAS400 = connAS400.CreateCommand()
CmdSAS400.CommandText = strProcedure
CmdSAS400.CommandType = CommandType.StoredProcedure
CmdSAS400.Parameters.Clear()
'CmdAS400.CommandTimeout = intTimeOut
If (prams Is Nothing) Then
Else
For Each parameter In prams
CmdSAS400.Parameters.Add(parameter)
Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
Thanks
View 3 Replies
View Related
Nov 2, 2015
Created a report that displays the Maximum Response time (example of value 00:00:00) which is directly pulled from the Stored proc.When I ran the report, the column displays blank values.I am not sure if I should add any conversion to the Response value in the report.
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
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
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
Jan 29, 1999
Periodically, while running a stored procedure from a VB front end, I get the error 08501, General Connection Failure.
This application is not being used by anyone else, but no one else running other apps on the same server has a problem.
Additionally a symptom dump is produced followed by a message that 'The current contents of process'input buffer are' sp_cursoropen'.
Any ideas?
Thanks in advance.
CF
View 2 Replies
View Related
Oct 5, 2006
I'm trying to build a quick stored procedure here but I get error with "BackupId"... Why?
Code:
CREATE PROCEDURE dbo.sp_tblBackupListINSERT
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
SET NOCOUNT OFF
INSERT INTO tblFileInvalid
(
BackupID, AccountNo, CompanyName, StoragePath, PhoneNumber, Active, RelayActive, TimeStamp
)
VALUES
(
'86','0607-2114-0910','zzUnknownCompanyName','E:BACKUPProcessed','0000000000','1','1','10/04/2006 6:30:00 PM'
)
/* RETURN */
View 2 Replies
View Related
Aug 17, 2004
does anyone see anything wrong with this stored procedure?? I keep getting a syntax error.
CREATE PROCEDURE [InsertGCTerms]
AS
INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title])
SELECT a.TM#, a.LASTNAME, a.FIRSTNAME, a.SSN#, a.JOBTITLE, a.HIREDATE, a.DEPT#
FROM GOVEMPLYS AS a
WHERE a.STATUS = 'TERMINATED'
RETURN
GO
View 3 Replies
View Related
May 21, 2008
Hi All,
I have a problem with the procedure shown below. When I try saving it I get the following error, can anyone help
Msg 102, Level 15, State 1, Procedure KillPurveyanceProcess, Line 19
Incorrect syntax near '@tSPId'.
ALTER PROCEDURE [dbo].[KillPurveyanceProcess]
AS
--SET NOCOUNT ON;
DECLARE @tSPId smallint;
DECLARE @tblocked smallint;
DECLARE my_cursor CURSOR FOR SELECT spid,blocked from sys.sysprocesses where program_name ='PurveyanceImport'
and status = 'suspended' and cmd = 'UPDATE'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
WHILE @@FETCH_STATUS = 0
BEGIN
Kill @tSPId
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
END
CLOSE my_cursor
DEALLOCATE my_cursor
Thanks in Advance
Neil
View 2 Replies
View Related
May 22, 2008
This procudere displaying error 'String or binary data would be truncated.'
ALTER PROCEDURE [dbo].[Maintenance_ManagePropertyActioned]
(@sortCol as varchar(50),
@SortOrder as varchar(10),
@Status as varchar(20),
@Fdate as varchar(20),
@TDate as varchar(20)
)
As
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @ExecuteSQL AS Varchar(Max)
Create Table #TempContractorTable
(
MRNumber varchar(50),
CallDate varchar(25),
ProblemNature varchar(500),
Responsible varchar(50),
TypeOfRequest varchar(10),
ContractorCode varchar(50),
ContractorName varchar(50),
PropertyAddress varchar(500)
)
SET @ExecuteSQL=''
SET @ExecuteSQL = @ExecuteSQL + '(SELECT CC.MRNumber As ''MReqNo'', Convert(Varchar,CC.CallDate,107) +'', ''+ CONVERT(CHAR(8),CC.CallTime,8)+ ''(GMT)'' As ''CallDateTime'', '
SET @ExecuteSQL = @ExecuteSQL + 'CC.ProblemNature, CC.Responsible,CC.TypeOfRequest, CC.ContractorCode, TLM.Title +'' ''+ CT.Fname + '' '' + CT.Surname As ''ContractorName'', '
SET @ExecuteSQL = @ExecuteSQL + 'PM.ApartmentNo + '','' + PM.FloorNumber + '','' + PM.[HouseName / No] + '','' + PM.[Street Line1] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'Replace(ISNULL(PM.[Street Line2],'''') ,ISNULL(PM.[Street Line2],''''),ISNULL(PM.[Street Line2],'''') + '','') + PM.[City / Town] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'PM.[County / State] + '','' + PM.[Post / Zip code] + '','' + C.CountryName As ''Property Address'' '
SET @ExecuteSQL = @ExecuteSQL + 'FROM HSSPMS_Tbl_Callcentre_Compliants AS CC '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_PropertyMaster AS PM ON CC.PropertyId = PM.PropertyId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Contractors AS CT ON CT.ContractorCode = CC.ContractorCode '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Country AS C ON PM.Country = C.CountryId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Title AS TLM ON TLM.id = CT.Title '
SET @ExecuteSQL = @ExecuteSQL + 'LEFT OUTER JOIN dbo.HSSPMS_TblTenantMaster AS TM ON CC.TenantId = TM.TenantCode '
SET @ExecuteSQL = @ExecuteSQL + 'WHERE CC.JobStatus = 1) UNION ALL '
SET @ExecuteSQL = @ExecuteSQL + '(SELECT CC.MRNumber As ''Maintenance Req No'', Convert(Varchar,CC.CallDate,107) +'', ''+ CONVERT(CHAR(8),CC.CallTime,8)+ ''(GMT)'' As ''CallDateTime'', '
SET @ExecuteSQL = @ExecuteSQL + 'CC.ProblemNature, CC.Responsible,CC.TypeOfRequest, CC.ContractorCode, TLM.Title +'' ''+ FCT.Fname + '' '' + FCT.Surname AS ''ContractorName'', '
SET @ExecuteSQL = @ExecuteSQL + 'PM.ApartmentNo + '','' + PM.FloorNumber + '','' + PM.[HouseName / No] + '','' + PM.[Street Line1] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'Replace(ISNULL(PM.[Street Line2],'''') ,ISNULL(PM.[Street Line2],''''),ISNULL(PM.[Street Line2],'''') + '','') + PM.[City / Town] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'PM.[County / State] + '','' + PM.[Post / Zip code] + '','' + C.CountryName As ''Property Address'' '
SET @ExecuteSQL = @ExecuteSQL + 'FROM HSSPMS_Tbl_Callcentre_Compliants AS CC '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_PropertyMaster AS PM ON CC.PropertyId = PM.PropertyId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FCT ON FCT.ContractorCode = CC.ContractorCode '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Country AS C ON PM.Country = C.CountryId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Title AS TLM ON TLM.id = FCT.Title '
SET @ExecuteSQL = @ExecuteSQL + 'LEFT OUTER JOIN dbo.HSSPMS_TblTenantMaster AS TM ON CC.TenantId = TM.TenantCode '
SET @ExecuteSQL = @ExecuteSQL + 'WHERE CC.JobStatus = 1) '
Insert InTo #TempContractorTable (MRNumber, CallDate, ProblemNature, Responsible, TypeOfRequest, ContractorCode, ContractorName, PropertyAddress)
EXEC (@ExecuteSQL)
SET @ExecuteSQL =' '
SET @ExecuteSQL = @ExecuteSQL + 'Select MRNumber From #TempContractorTable '
IF (@Status = 'Load')
BEGIN
SET @ExecuteSQL = @ExecuteSQL + ' Where datediff(day, CallDate, getdate()) > 5 ORDER BY '+ @SortCol + ' ' + @SortOrder + ''
END
IF (@Status = 'Search')
BEGIN
IF LEN(@Fdate)>0 AND LEN(@Tdate)>0
BEGIN
SET @ExecuteSQL=@ExecuteSQL + ' Where CallDate >= ''' + @FDate + ''' AND CallDate <= ''' + @TDate + ''' '
END
ELSE IF LEN(LTRIM(RTRIM(@Fdate)))>0
BEGIN
SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate >= ''' + @FDate + ''' '
END
ELSE IF LEN(LTRIM(RTRIM(@Tdate)))>0
BEGIN
SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate <= ''' + @TDate + ''' '
END
SET @ExecuteSQL=@ExecuteSQL + 'ORDER BY '+ @SortCol + ' ' + @SortOrder + ' '
END
--EXEC (@ExecuteSQL)
PRINT (@ExecuteSQL)
END
any one give me a solution
Regards,
Prabu R
View 2 Replies
View Related
Mar 26, 2007
error in stored procedure
an error or column name is missing
for SELECT INTO statement,verify each column has name,for other statements look for empty alias name alies defiened as "" or []
add a name or single space as the alias name,an object name or column is missing or empty
stored procedure
ALTER PROCEDURE dbo.spM3_ByLocationSearch
@DEPOTTRACK_CREATEDDATE VARCHAR(50) = NULL,
@depottrack_locid VARCHAR(50) = "",
@OrderBy VARCHAR(40) = NULL
AS
SET NOCOUNT ON
DECLARE @Select NVARCHAR(4000),
@Where NVARCHAR(4000),
@SqlFinal NVARCHAR(4000),
@ErrorMsgID INT,
@ErrorMsg VARCHAR(200)
SET @Select=''
SET @Where=''
SET @Select = 'WITH SearchLocList AS
(select * from
(
SELECT dp.depottrack_inventory_serial_number
FROM depottrack DP, (SELECT depottrack_inventory_serial_number
,MAX(DEPOTTRACK_CREATEDDATE) AS DEPOTTRACK_CREATEDDATE
FROM depottrack
) a
left join
(
SELECT
I.INVENTORY_SERIAL_NUMBER AS CONTAINER#,
product_class+ ' | ' + product_description as SIZE | TYPE,
inventory_status as STATUS,
inventory_vendor_id as VENDOR,
ISNULL(INVENTORY_COST_PURCHASE,0) AS PURCHASE PRICE,
ISNULL(INVENTORY_COST_UPFIT,0) + ISNULL(INVENTORY_COST_MATERIAL,0) AS UPFIT,
ISNULL(INVENTORY_COST_DRAYAGE,0) AS DRAYAGE,
ISNULL(INVENTORY_COST_PURCHASE,0) + ISNULL(INVENTORY_COST_UPFIT,0) +
ISNULL(INVENTORY_COST_MATERIAL,0) + ISNULL(INVENTORY_COST_DRAYAGE,0)
AS TOTAL
from
INVENTORY I LEFT JOIN VW_IV_COST_PURCHASE P on I .INVENTORY_GUID =
P.INVENTORY_GUID
LEFT JOIN VW_IV_COST_UPFIT U on I .INVENTORY_GUID = U.INVENTORY_GUID
LEFT JOIN VW_IV_COST_DRAYAGE D on I .INVENTORY_GUID = D.INVENTORY_GUID
LEFT JOIN VW_IV_COST_MATERIAL M on I .INVENTORY_GUID = M.INVENTORY_GUID
left join product on i.inventory_product_id = product.product_id
)
b
on a.depottrack_inventory_serial_number = b.CONTAINER#
where container# is not null
order by depottrack_inventory_serial_number )
Select * from SearchLocList'
IF (@DEPOTTRACK_CREATEDDATE IS NOT NULL)
BEGIN
IF (@Where <>'' )
SET @Where =@Where + 'SearchLocList.DEPOTTRACK_CREATEDDATE <=''' + CONVERT(VARCHAR, @DEPOTTRACK_CREATEDDATE,101) + ''''
ELSE
SET @Where = 'SearchLocList.DEPOTTRACK_CREATEDDATE <= ''' + CONVERT(VARCHAR,@DEPOTTRACK_CREATEDDATE,101) + ''''
END
/*CONVERT(VARCHAR(10),DP.depottrack_createddate,101) <= */
IF(@depottrack_locid<>"")
BEGIN
IF (@Where <>'' )
SET @Where =@Where + ' AND SearchLocList.depottrack_locid=''' + @depottrack_locid + ''''
ELSE
SET @Where = 'AND SearchLocList.depottrack_locid =''' + @depottrack_locid+ ''''
END
IF ((@DEPOTTRACK_CREATEDDATE IS NOT NULL))
BEGIN
IF (@Where <>'' )
SET @Where =@Where + 'SearchLocList. CONVERT(VARCHAR(10),DP.depottrack_createddate,101) <=''' + CONVERT(VARCHAR, @DEPOTTRACK_CREATEDDATE,101) + ''''
ELSE
SET @Where = 'SearchLocList. CONVERT(VARCHAR(10),DP.depottrack_createddate,101)<=''' + CONVERT(VARCHAR,@DEPOTTRACK_CREATEDDATE,101) + ''''
END
IF (@OrderBy IS NULL)
BEGIN
SET @OrderBy= 'SearchLocList.DP.depottrack_createddate, DP.depottrack_inventory_serial_number'
END
IF (@Where <> '')
BEGIN
SET @SqlFinal= @Select + ' where ' + @Where + ' order by ' + @OrderBy
END
ELSE
BEGIN
SET @SqlFinal= @Select + ' order by ' + @ORDERBY
END
PRINT @SqlFinal
EXEC SP_EXECUTESQL @SqlFinal
RETURN
View 3 Replies
View Related
