SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM
property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id
WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
@pc2.prop_char_typ_code = 'USECD' AND
(@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
@pc2.tax_year = '2008' AND
@pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')
Hi, I'm having trouble with this multi-statement table-valued function:
ALTER FUNCTION MakeArDetail ( -- Add the parameters for the function here @dateStart DATETIME, @dateEnd DATETIME ) RETURNS @arDetail TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) AS BEGIN DECLARE @arTemp TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 )
INSERT INTO @arTemp SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims; UPDATE @arTemp SET Claim = (SELECT SUM(Pims.AmtReq) FROM Pims WHERE Pims.Insurer = @arTemp.Insurer AND Pims.NABP = @arTemp.NABP AND Pims.NumRx = @arTemp.NumRx );
INSERT INTO @arDetail SELECT * FROM @arTemp RETURN END GO
I get Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43 Must declare the scalar variable "@arTemp".
I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared. If I don't include the UPDATE command the thing works.
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @Date DateTime means??? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value??? is that correct or is it more to it????
Hi everyone, I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised in the body of the script. I post the relevent code below: SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int)) sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@LoginName").Value = userName sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime)) sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection) C4LConnection.Open() CandidateID = sqlSetCandidateEmail.ExecuteScalar() Catch Exp As SqlException lblResults.Visible = True lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script. Any help would be appreciated.
Need a little help! I am trying to insert ListItems values from a DropDownList into a database table. However in the code behind I am continuosly met with the error Name 'ddltest' is not declared. As you can see from the code below ddltest is an object with the ID ddltest. What am I doing wrong? Protected Sub ddltest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim MyVar As String = ddltest.SelectedItem.Value
If MyVar = "" Then ErrorMessage.Text = "Please select a test" Else 'Insert selection into databaseDim oConnection As New SqlConnection Dim oCommand As SqlCommand Dim sConnString As String Dim sSQL As String sConnString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|xxxxx.mdf;Integrated Security=True;User Instance=false"oConnection = New SqlConnection(sConnString) sSQL = "INSERT INTO testDB(Myxxxx) Values (@Myxxxx)" oConnection.Open()oCommand = New SqlCommand(sSQL, oConnection)oCommand.Parameters.Add(New SqlParameter("@Myxxxx", MyVar)) oCommand.ExecuteNonQuery() oConnection.Close() ErrorMessage.Text = "You selected " & MyVar & " and it has been added to the database." End If End Sub
Is there any way to create a cursor, based on a dynamically created select_statement? Something like: DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect where @strSelect is previously declared as let's say varchar. I don't want to create a stored procedure for this.
OMG i'm so stupid, i edited my original post instead of replying!!
I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.
I know how to set a variable for the entire query, but how do I set one per row?
I have one query which is growing beyond my ability to understand/maintain it. I factor in the sale price of an item based on cost, our markup, shipping & channel fees. The problem is that each one of these has it's own variability. I would like to be able to store inline calculations as a variable such that I can call & add them. Example:
SELECT product ,price ,Cast(price * 1.1 as decimal(18,2)) as markup ,@markup + 5 as 'markup-and-shipping' -- <-- This factors in all of the previous calculations, plus something new. FROM #tempPrice
i try to execute this one it does not work said that Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@fdas'. how to fix that one? by not replacing the @fdas
Hi, I'm trying to create a function that returns a table, however I wantto use a local variable in there and enterprise manager ain't liking it!The error I get is number 156 'incorrect syntax near the keyword'declare'.. hopefully this is just a simple thing where I've put it inthe wrong place.The code follows:CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@datefromsmalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @typenvarchar(20))RETURNS TABLE ASRETURNdeclare @set nvarchar(150)select "Total Pledged" as info, sum(total) as totFROM AFresponseTotals (@campaign, @datefrom, @dateto,@prospect)Cheers for any help,Chris
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open dbname_name
fetch next from dbname_name
into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =
'
select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+'
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.
The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.
The MemberId field is a varchar(20) in the table.
Create procedure sp_GetClaims_BY_MemberID @Memberid varchar (50) as Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible where Membid = @memberid
EXEC sp_GetClaims_BY_MemberID '99999999999'
The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?
Must declare the scalar variable "@Id_CostEmployee".
I'm tired of "googling" this error, and I've tried all the advices, nothing... I don't know what is happening here, I have 5 other forms, all simillar and they all work!
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function? Here's what I did... Dim p_s_syounin2 As New SqlCommand Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23") '''' connection string is not placed here acc. to my research End If
End Sub
Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand
If e.CommandName = "Approve" Then
cnn.Open() p_s_syounin2.ExecuteNonQuery()
cnn.Close()
End If
End Sub
I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. "
I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.
Hi with the code below I am getting the error Error inserting record. Must declare the scalar variable "@contractWHERE" I removed @contract and it then gave me the error Error inserting record. Must declare the scalar variable "@zipWHERE" I was wondering if some can point me in the right direction for fixxing this protected void cmdUpDate_Click(Object sender, EventArgs e) { //Define ADO.NET Objects. string updateSQL; updateSQL = "UPDATE Authors SET "; updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, "; updateSQL += "phone=@phone, address=@address, city=@city,state=@state, "; updateSQL += "zip=@zip, contract=@contract"; updateSQL += "WHERE au_id@au_id_original"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(updateSQL, con); //Add the parameters. cmd.Parameters.AddWithValue("@au_id", txtID.Text); cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text); cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text); cmd.Parameters.AddWithValue("@phone", txtPhone.Text); cmd.Parameters.AddWithValue("@address", txtAddress.Text); cmd.Parameters.AddWithValue("@city", txtCity.Text); cmd.Parameters.AddWithValue("@state", txtState.Text); cmd.Parameters.AddWithValue("@zip", txtZip.Text); cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked)); cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value); //Try to open the database and execute the update try { con.Open(); int updated = cmd.ExecuteNonQuery(); lblStatus.Text = updated.ToString() + " records inserted."; } catch (Exception err) { lblStatus.Text = "Error inserting record. "; lblStatus.Text += err.Message; } finally { con.Close(); } } }
DECLARE @SendTo VarChar(4000) SET @SendTo = '' SELECT DISTINCT @SendTo = @SendTo + UserEmail + ';' FROM dbo.tbl_AccountInfo WHERE (UserEmail <> '') PRINT @SendTo
The purpose of this code is to build up a ; seperated string of email adresses that I can use sending mail from SQL server.
It works but it only give me one record (should give me 130 records) , but if I remove the DISTINCT part it give me all records, duplicates too. Does anyone know why and how can I get this to work? Or maybe do it in another way?
I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement.
these attempts did not work:
DECLARE crsCursor CURSOR FOR @vchrSQL DECLARE crsCursor CURSOR FOR (@vchrSQL)
Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?
I have a select statement that works fine. Now I need to create a view with this statement and I have problems with the variables that I need to declare.
Is there a way to include the declare command in a view?
I can not use a stored procedure this time.
This is the statement:
DECLARE @FirstDate DATE, @SecondDate DATE, @ThirdDate DATE SELECT @FirstDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones ---SELECT @SecondDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones WHERE fecha_valor<> @FirstDate SELECT @SecondDate = dateadd(week,-1,max(fecha_valor)) FROM MPR_Historico_Posiciones
OK i have my stored procedure all set up and working.. But when i try and add a second variable called @iuser and then after i execute the stored procedure, i get an error saying:- ERROR "Must declare scalar variable @iuser"
Here is the code i am using in my stored proc, also my stored proc worked fine before i used a second variable??!
//BEGIN
ALTER PROCEDURE [dbo].[putpending] (@cuuser nvarchar(1000), @iuser nvarchar(1000))
And i know my VB.NET code is working but i will put it in anyway:-
//BEGIN
'variables Dim user As String user = Profile.UserName Dim intenduser As String intenduser = DetailsView1.Rows(0).Cells(1).Text.ToString()
'connection settings Dim cs As String cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True" Dim scn As New SqlConnection(cs) 'parameters Dim cmd As New SqlCommand("putpending", scn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@cuuser", SqlDbType.NVarChar, 1000) cmd.Parameters("@cuuser").Value = user cmd.Parameters.Add("@iuser", SqlDbType.NVarChar, 1000) cmd.Parameters("@iuser").Value = intenduser 'execute scn.Open()
