[|-)] Error: Stored Procedure Has Too Many Argument....
Feb 11, 2008
Hello frend...i have a problem with my application when i'm trying to delete a certain data from gridview...i have a store procedure that already create in my mssql server...and in datasource i'm using command that i've already created in my mssql server...the problem is i dont know how i can send my value to the parameter in datasource.....and one more thing what is exaclty the error with 'Store procedure has <my function> too many argument" occur? Please help me....so i paste my code below to easier and detect my problem....(sorry my english are no good)....
2) And this is my Store procedure that i created in mssql server
Store Procedure: Roles_view
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Roles_view]
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Role
END
Store Procedure: Roles_delete
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Roles_delete]
@roleName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Role
WHERE Role_name LIKE @roleName
END
3) The problem is when i'm trying to delete my certain data, the message box appear and say "Procedure or function Roles_delete has too many argument specified".what should i do????anabosy please help me..
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField FROM myTable WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
Select columnname from tablename order by ordercolumn
We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.
The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:
Select @userCount = COUNT(*) from permissions where UserID = @userID and (projectid = @projectID or projectid=0) and clientid = @clientID and folderpermissions in (dbo.sp_FoldersOfFile(@fileID))
The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.
Hey, friends, i have a problem on my bank transaction page now: Procedure or function has too many argument specified.people can log in by their usernames, 1 username can have many accounts, there are 2 account types:'c', 's'. after people login, they are only allowed to withdraw money from their own account: here is some of my codes: 1 else if (DropDownList3.SelectedItem.Text == "withdraw")2 {3 SqlConnection sqlCon = new SqlConnection("Data Source=bandicoot.cs.rmit.edu.au;Initial Catalog=shuli;User ID=test;Password=test");4 SqlCommand cmd = new SqlCommand("usp_withdraw", sqlCon);5 string spResult = "";6 cmd.CommandType = CommandType.StoredProcedure;7 cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = Session["username"].ToString();8 cmd.Parameters.Add("@AccountFrom", SqlDbType.VarChar).Value = TextBox3.Text;9 cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = TextBox5.Text;10 cmd.CommandType = CommandType.StoredProcedure;11 sqlCon.Open();12 spResult = cmd.ExecuteScalar().ToString();13 if (string.Compare(spResult, "Execute successfully") == 0)14 {15 SqlDataSource with = new SqlDataSource();16 with.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString();17 18 with.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('W','" + TextBox3.Text + "','" + TextBox3.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() + "')";19 int inertRowNum = with.Insert();20 Server.Transfer("~/deposit_withdraw_confirm.aspx");21 }22 else23 {24 Server.Transfer("~/deposit_withdraw_error.aspx");25 }26 here is my store procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER Procedure [dbo].[usp_withdraw]( @AccountFrom Varchar(20), @Amount Decimal(10,2))AsDECLARE @ReturnMsg AS VARCHAR(20)DECLARE @balance AS Decimal(10,2)DECLARE @username AS nvarchar(50)SELECT @balance = balanceFROM Account,LoginWHERE Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFromIF ( SELECT AccountType FROM Account, Login Where Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFrom ) ='c'BEGIN BEGIN TRAN IF (@balance - @Amount) > 199 BEGIN UPDATE Account SET balance= balance-@Amount WHERE AccountNumber=@AccountFrom And (balance - @Amount) > 199 SET @ReturnMsg = 'Execute successfully' COMMIT TRAN END ELSE BEGIN SET @ReturnMsg = 'Execute with error' ROLLBACK TRAN END SELECT @ReturnMsg ENDIF ( SELECT AccountType FROM Account Where AccountNumber = @AccountFrom ) ='s'BEGIN BEGIN TRAN IF (@balance - @Amount) > 0 BEGIN UPDATE Account SET balance= balance-@Amount WHERE AccountNumber=@AccountFrom And (balance - @Amount) > 0 SET @ReturnMsg = 'Execute successfully' COMMIT TRAN END ELSE BEGIN SET @ReturnMsg = 'Execute with error' ROLLBACK TRAN END SELECT @ReturnMsg ENDI think the problem is in Line 7cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = Session["username"].ToString();this is to get the current login user's uername.
hai <br> <p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p> CREATE PROCEDURE outputtable @tablename nchar(10) AS<br> begin<br> .........................<br> insert into @tablename (classno) values (@cno)<br> .................................<br> END<br>
<p> I get the following error message :</p> Msg 1087, Level 15, State 2, Procedure................<br> Must declare the table variable "@tablename".<br> <p> Is there anyway i can get around this one. I will be grateful if anyone can help me</p>
hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc... Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute.
the code is this:
Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click Dim report As Byte() = Nothing
'Create an instance of the Reporting Services 'Web Reference Dim rs As localhost.ReportingService = New localhost.ReportingService 'Create the credentials that will be used when accessing 'Reporting Services. This must be a logon that has rights 'to the Axelburg Invoice-Batch Number Report. '***Replace "LoginName", "Password", and "Domain" with 'the appropriate values. *** rs.Credentials = New _ System.Net.NetworkCredential("Administrator", _ "password", "localhost") rs.PreAuthenticate = True
'The Reporting Services virtual path to the report. Dim reportPath As String = _ "/Galactic Delivery Services/Axelburg/Invoice-Batch Number"
' The rendering format for the report. Dim format As String = "html4.0"
'The devInfo string tells the report viewer 'how to display with the report. Dim devInfo As String = _ "<DeviceInfo>" + _ "<Toolbar>False</Toolbar>" + _ "<Parameters>False</Parameters>" + _ "<DocMap>True</DocMap>" + _ "<Zoom>100</Zoom>" + _ "</DeviceInfo>"
'Create an array of the values for the report parameters Dim parameters(1) As localhost.ParameterValue Dim paramValue As localhost.ParameterValue _ = New localhost.ParameterValue paramValue.Name = "StartDate" paramValue.Value = calStartDate.SelectedDate parameters(0) = paramValue paramValue = New localhost.ParameterValue paramValue.Name = "EndDate" paramValue.Value = calEndDate.SelectedDate parameters(1) = paramValue
'Create variables for the remainder of the parameters Dim historyID As String = Nothing Dim Credentials() As localhost.DataSourceCredentials = Nothing Dim showHideToggle As String = Nothing Dim encoding As String Dim mimeType As String Dim warnings() As localhost.Warning = Nothing Dim reportHistoryParameters() As _ localhost.ParameterValue = Nothing Dim StreamIDs() As String = Nothing
Dim sh As localhost.SessionHeader = _ New localhost.SessionHeader rs.SessionHeaderValue = sh
Try 'Execute the report. report = rs.Render(reportPath, format, historyID, _ showHideToggle, encoding, mimeType, _ reportHistoryParameters, warnings, _ StreamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
'Flush any pending responce. Response.Clear()
'Set the Http headers for a PDF responce. HttpContext.Current.Response.ClearHeaders() HttpContext.Current.Response().ClearContent() HttpContext.Current.Response.ContentType = "text/html" ' filename is the default filename displayed 'if the user does a save as. HttpContext.Current.Response.AppendHeader( _ "Content-Disposition", _ "filename=""Invoice-BatchNumber.HTM""")
'Send he byte array containing the report 'as a binary response.
Catch ex As Exception If ex.Message <> "Thread was being aborted." Then HttpContext.Current.Response.ClearHeaders() HttpContext.Current.Response.ClearContent() HttpContext.Current.Response.ContentType = "text/html" HttpContext.Current.Response.Write( _ "<HTML><BODY><H1>Error</H1><br><br>" & _ ex.Message & "</BODY></HTML>") HttpContext.Current.Response.End()
End If End Try
End Sub End Class
This is the area underlined as failing: report = rs.Render(reportPath, format, historyID, _ showHideToggle, encoding, mimeType, _ reportHistoryParameters, warnings, _ StreamIDs)
The errors all pretty much go like this: c:inetpubwwwrootAxelburgFrontEndReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'.
I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.
I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong.
What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select....Where function?
After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind().
here's some of the code:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) if Not Page.IsPostBack then dim Code as String Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code")) label1.Text = Code GetPropertyCodes(Code) ddlPropertyCode.DataBind() end if End Sub
Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @Code)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_code.ParameterName = "@Code" dbParam_code.Value = Code dbParam_code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_code)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.
I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the wkdy20070416-a.rm filename so that when it's all said and done that entry would read:
Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.
Code Snippet
UPDATE T_Programs_TestCopy (SET RealAudioLink = REPLACE(RealAudioLink, '-a', '') AND (SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA') WHERE (RealAudioLink LIKE 'wkdy%'))
I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.
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.
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]
How can I make it work when I need to pull out a field which is text type using a stored procedure? Please help!!!Thanks I am getting the following error Error 409: The assignment operator operation could not take a text data type as an argument ===========my sp================================= CREATE PROCEDURE [dbo].[sp_SelectABC] (@a varchar(50) output, @b text output ) AS set nocount on select @a=name, @b= description from ABC
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.
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
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
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)
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
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
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
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
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 @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'
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?
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(); }
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?
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.
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.
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?
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
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
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 + ' '
