Correct Syntax For Parameters When Creating SQL Job
Dec 30, 2007
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.
Here's a snippet from the stored procedure that creates the job:
CREATE PROCEDURE [dbo].[spArchive]
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE): ///--MuCh14spInvTotal3.sql--/// USE AP --AP Database is installed in the SSMSE-- GO CREATE PROC spInvTotal3 @InvTotal money OUTPUT, @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' AS
IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices JOIN Vendors WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar) GO /////////////////////////////////////////////////////////////// Then I executed it and I got the following error: Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12 Incorrect syntax near the keyword 'WHERE'. I do not know what wrong with it and how to correct this problem.
HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
This is a Stored proc I am working on I dont know much about stored procs. This is what I am trying to achieve The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30), @NextAction ntext output ) AS BEGIN
SET NOCOUNT ON; Declare @ID int; @ID = Select Sequence from [dbo].[Track] where Code=@Code; Declare @nextCode varchar; @nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1); @NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode; return @NextAction;
END GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this. Thanks
If someone can show me the correct syntax for the right side of the code below, I'd appreciate it. srcMoreInfo.InsertParameters("Address").DefaultValue = DirectCast(cuwMoreInfo.FindControl("txtAddress"), TextBox).Text
WHATS WRONG IN THIS CODE ? --------------------------------- PS: I'm getting this error: -------------------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7 Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES XYZ Company ABC Limited Liability Company ZZZ Corporation KKK Inc. ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS Company Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES XYZ Company ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT * FROM [Names of Companies] WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
I try to call the storeproc to perform task by allowing only input 6 of length. The syntax over here might not be relevant, im seeking for correct syntax. webform Dim connString2 As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection2 As New SqlConnection(connString2) Dim test01 As IntegerDim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2) myPuzzleCmd2.CommandType = CommandType.StoredProcedure Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6) < -------- allow 6 letters in length to be input retLengthParam.Direction = ParameterDirection.InputmyPuzzleCmd2.Parameters.Add(retLengthParam) Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30) retRandomCode.Direction = ParameterDirection.Output myPuzzleCmd2.Parameters.Add(retRandomCode) TryDim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader() myPuzzleCmd2.ExecuteNonQuery() Catch ex As Exception Response.Write("sp value : " & retRandomCode.Value) Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1) iRandomCode(1) = Convert.ToString(retRandomCode.Value)myPuzzleCmd2 = Nothing Session.Remove("RandomCode") HttpContext.Current.Session("RandomCode") = iRandomCode(1) Response.Write(Session("RandomCode")) Finally myConnection2.Close() End Try End Using
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number). I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance. The database table is called "friday" and the one and only column is called "Tickets". Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn; conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"); conn.Open();int count = -1; SqlCommand cmd = new SqlCommand("select Tickets from friday", conn); count = (int)cmd.ExecuteScalar();if (count > 0) { string updateString = @" update friday set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString); cmd2.Connection = conn; cmd2.ExecuteNonQuery(); } else { } conn.Close(); }
I am having trouble finding the correct syntax to access a variable. I have a variable defined in the Variables window: The variable name is formatedDate. The DataType is String.
I am successfully setting the value of the variable in a Execute SQL Task. The SQL is as follows:
SELECT LEFT(CONVERT(VARCHAR, MAX(ReportDate), 120), 10) as formatedDate from DimReportDates
The Result Set is set to “Single Row” and properly set up.
No problem so far. I can see with a watch that the variable has the correct value, something like:
‘2015-09-30’
Now, in a subsequent step, a Data Flow Task, I want to access the variable. Actualy it is in the SQL statement of a OLE DB source in the Data Flow… I have the following:
I have 13 parameters, ordered correctly within the Report Parameter screen.
When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly. The remainder are ordered correctly.
I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.
Please can someone suggest how I can get the parameters to appear in the correct order within the application?
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select * from VendTable1 a join ( select firstname, lastname from VendTable1 group by firstname, lastname having count(*) > 1 ) b on a.firstname = b.firstname and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
I work at a place which is currently running SQL 2000, but they areplanning to migrate to 2k5. I was thinking that this is the perfectopportunity to fix all the weaknesses we have had in our data model forthe longest: primary keys and foreign keys with different names, use ofcharacter columns for boolean fields, use of integer columns fortoggles, no referential integrity, etc.So, even if I create my Utopian perfect data model and modify all ofour data loaders to use it, our live website must use the old incorrectversion because there is way too much work involved in redoing thecode.My question then becomes: if I have a correct version, how easy andwith what approach would one take the data in the correct one andmirror it to the poorly designed schema?
Status as the row heading and month as the column heading. The detail is a count off the records which fall into these statuses and the month columns.
The detail also contains a navigation link to a subreport which returns the records which make up the count.
For example if the detail contains a 7 , when i click on the 7 it returns 7 records with details off these records. The subreport accepts 2 parameters which is passed over from the navigation link, the parameters are - status id and month.
My question is - I have put a total on the matrix for the month columns, when i click on the total value the subreport appears (as the total is treated as part off the details) but it does not return the correct number off records. Im unsure as to why the total would not return all records , but it seems it is not passing the month parameter correctly, it seems to not to know to pass 'all' months which appear on the matrix as it is the 'total'. It seems to get confused and returns records only for a particular month. I thought the matrix would be smart enough to know that when a total has a navigation to return all records.
Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating Dim quantity As Integer Dim weight As Integer Dim packed As Integer quantity = CInt(bagsOnPallet.Text) weight = CInt(lbsInBags.SelectedValue) packed = weight * quantity e.Command.Parameters("@packedLbs").Value += packed e.Command.Parameters("@AvailableLbs").Value -= packed End Sub
I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?
I'm mostly an Oracle and PL/SQL type so I'm a little lost here...
We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:
CASE APTran.LineNbr WHEN < '0' THEN 'O' --Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry). WHEN > '0' THEN 'D' --Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry). ELSE 'E' END AS JED_DIST_OR_OFF,