DECLARE CURSOR
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.
Thanks!
View Complete Forum Thread with Replies
Related Forum Messages:
Help With Declare And Cursor
I keep getting the message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'declare'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'declare'. What am I doing wrong? declare @dbname varchar(8000), declare @countyname varchar (200) , declare @sql varchar(8000) declare county_name cursor for select distinct county from Zipcodes open county_name fetch next from county_name into @countyname declare dbname_name cursor for 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' exec (@sql) end set @sql = '' fetch next from dbname_name into @dbname fetch next from county_name into @countyname CLOSE county_name DEALLOCATE county_name CLOSE dbname_name DEALLOCATE dbname_name
View Replies !
Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway? Is Declare Cursor same as Create Cursor and if not what is the major difference?
View Replies !
Declare Cursor With Dynamic SQL?
Hello.. Can you declare a cursor with dynamic SQL? 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?
View Replies !
Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
Declare Cursor For Execute Stored_procedure
Hello, I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @Variable). Something like this: DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @Variable i get an error saying "incorrect syntax near the keyword 'EXECUTE'." cannot get rid of the error. what am i doing wrong? (i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl) Not sure if i am doing this right all together. any help would be greatly appreciate.
View Replies !
How To Declare Cursor In Stored Procedure?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure. CREATE PROCEDURE STP_EMPSAL @empno int, @Employee_Cursor CURSOR VARYING OUTPUT FOR SELECT empno FROM AdventureworksDW.dbo.emp AS OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor into @empno; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN UPDATE emp set sal= sal+ 2000 where empno = @empno and comm is null mgr='Scott'; FETCH NEXT FROM Employee_Cursor into @empno; COMMIT; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor;
View Replies !
Declare Cursor Based On Dynamic Query
Hi, I am declaring the cursor based on a query which is generated dynamically. but it is not working Declare @tempSQL varchar(1000) --- This query will be generated based on my other conditon and will be stored in a variable set @tempsql = 'select * from orders' declare cursor test for @tempsql open test This code is not working. please suggest Nitin
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration. There error generated is: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor Here is the cursor declaration: declare some_cursor CURSOR for select * from part_view FOR UPDATE Any ideas, guys? Thanks in advance for knocking your head against this one. PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View Replies !
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
Declare @var????
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???? CREATE PROCEDURE dbo.Tracking_GetStatus AS DECLARE @Date DateTime DECLARE @Begining DateTime DECLARE @Ending DateTime SET @Date = GETDATE() SET @Begining = DATEADD(ss,(DATEPART(ss,@Date)*-1), DATEADD(mi,(DATEPART(mi,@Date)*-1), DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date))) SET @Ending = DATEADD(ss,-1, DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@Date)*-1), DATEADD(mi,(DATEPART(mi,@Date)*-1), DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date))))) SELECT Vehicl, UpdateTi XCoord, YCoord, Status FROM Track WHERE UpdateTime >= @Begining AND UpdateTime <= @Ending RETURN GO
View Replies !
DECLARE In SQL CE
Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints? Matt
View Replies !
My DropDownList Won't Declare?
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 <asp:TemplateField HeaderText="Test"> <EditItemTemplate> <asp:DropDownList ID="ddltest" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddltest_SelectedIndexChanged" > <asp:ListItem Selected="True" Value="" ><-- Please Select a test --></asp:ListItem><asp:ListItem Value="1">1</asp:ListItem> <asp:ListItem Value="2">2</asp:ListItem> <asp:ListItem Value="3">3</asp:ListItem> <asp:ListItem Value="4">4</asp:ListItem> <asp:ListItem Value="5" >5</asp:ListItem> </asp:DropDownList> </EditItemTemplate> </asp:TemplateField>
View Replies !
Must Declare Scalar @.....
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.
View Replies !
'declare' Syntax In A UDF
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
View Replies !
Declare @var Text
Hi, I want to store more than 8000 characters. Ideally it might have been fine if decalre @var text had worked for me. Can anyone tell is it possible.... thanks, sajai.
View Replies !
Declare Variable For All In SP
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? I am currently working in SQL 2000.
View Replies !
Declare Variable
Declare @DBName varchar(25) select @DBName = 'Production' Select @DBName = @DBName + '.dbo.'+'sysfiles' select @DBName select * from @DBName When I executes above lines of code in query analyser it give me an error like : Server: Msg 137, Level 15, State 2, Line 5 Must declare the variable '@DBName'. give me solution as soon as possible Thanks Aric
View Replies !
Why Declare Does Not Work
declare @fdas as varchar(10) set @fdas = 'master' use @fdas 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 but i got master database ------------ what is the difference actually between use master than the first querry? arifliminto86
View Replies !
Declare Table
Dear All, actually, in our application, we are using so many temporary local variables like @table_name.... and based on these local variables, we are making joins with the regular adtabase tables. is it better thing instead of temp tables? what exactly i want to know is, @table_name,#temp_table,##temp_table..... in these three processes, which one is the best one... thanks in advance Vinod Even you learn 1%, Learn it with 100% confidence.
View Replies !
Declare Table
When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help. Declare @pc2 Table ([prop_char_typ_code] [varchar](5) NOT NULL, [tax_year] [varchar](4) NOT NULL, [property_id] [int] NOT NULL, [id] [int] IDENTITY(1,1) NOT NULL, [value] [varchar](100) NULL, [prop_segment_id] [int] NULL) Insert into @pc2 ([prop_char_typ_code], [tax_year], [property_id], [id], [value], [prop_segment_id]) Select [prop_char_typ_code], [tax_year], [property_id], [id], [value], [prop_segment_id] from property_char Go 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')
View Replies !
Declare A Concatenated Value.
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. Example. Table 1. Column1 (Modem) Column2 (Highspeed) Column3 (Black) Concatenated to be MODEM HIGHSPEED BLACK. The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory. Column1 (MODEM HIGHSPEED BLACK) Column2 (44)
View Replies !
Must Declare The Scalar Variable
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(); } } } Many Thanks in advance
View Replies !
Must Declare The Scalar Variable
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.
View Replies !
How To Declare Global Sqlcommands...
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. " Please help me. Thanks guys. Audrey
View Replies !
Must Declare The Scalar Variable
Hello! I have a aspx page in which I have a Gidview populated by a sqlDataSouce. This is my code: <%@ Page Language="VB" AutoEventWireup="false" CodeFile="CostEmployee1.aspx.vb" Inherits="RecursosHumanos_CostEmployee1" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Style="z-index: 100; left: 0px; position: absolute; top: 0px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Actualizar" style="color: white"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancelar" style="color: white"></asp:LinkButton> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Apagar" Text="Apagar" runat="server" CommandName="Delete" OnClientClick='return confirm("Tem a certeza que deseja apagar este registo?");' CausesValidation="false"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_CostEmployee" InsertVisible="False" SortExpression="Id_CostEmployee"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Id_CostEmployee") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id_CostEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_Employee" SortExpression="Id_Employee"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FullName" SortExpression="FullName"> <EditItemTemplate> <asp:TextBox ID="textbox5" runat="server" Text='<%# Bind("FullName")%>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("FullName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="NumEmployee" SortExpression="NumEmployee"> <EditItemTemplate> <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Period" SortExpression="Period"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Period") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Period") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="CostHour" SortExpression="CostHour"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("CostHour") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("CostHour") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Date" SortExpression="Date"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Bind("Date") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EuroscutConnectionString %>" SelectCommand="SELECT [HR.CostEmployee].Id_CostEmployee, [HR.CostEmployee].Id_Employee, [HR.CostEmployee].Period, [HR.CostEmployee].CostHour, [HR.CostEmployee].Date, [HR.Employee].FullName, [HR.Employee].NumEmployee FROM [HR.CostEmployee] INNER JOIN [HR.Employee] ON [HR.CostEmployee].Id_Employee = [HR.Employee].Id_Employee" UpdateCommand="UPDATE [HR.CostEmployee] set Period = @Period, CostHour = @CostHour where Id_CostEmployee = @Id_CostEmployee" DeleteCommand="DELETE from [HR.CostEmployee] where (Id_CostEmployee = @Id_CostEmployee)"> <UpdateParameters> <asp:Parameter Name="Period" /> <asp:Parameter Name="CostHour" /> <asp:Parameter Name="Id_CostEmployee" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="Id_CostEmployee" Type="int32" /> </DeleteParameters> </asp:SqlDataSource> </div> </form></body></html> When I run the page I'm able to edit the row but when I try to delete it gives me the error: 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! Any suggestions, pleeeeaaaase? Thank's! Paula
View Replies !
Must Declare The Scalar Variable
Hi, I found a nice tutorial on the net on how storing images to a SQL DB (http://blogs.msdn.com/jdixon/articles/495408.aspx) but have some troubles in retreiving the image back from the database. Once I click the View details link, I receive an error message "Must declare the scalar variable @..." Can someone tell me how I can define this scalar variable and/or how I can display an image from my database ? Thanks to all !!! Bart
View Replies !
Declare Variable Dynamically
I'm attempting to modify some Crosstab generating code, and I need someadvice/examples.Currently, the code uses a single string variable to store thedynamically generated query (www.johnmacintyre.ca). The problem is thatI am trying to pivot biological taxonomy information, and may end upwith a table containing over 200 columns. This takes the dynamic stringwell over the 8000char limit for variables.[color=blue]>From my understanding, the EXEC() command does not have the 8000char[/color]limit if the execution string is broken into chunks, and concatenatede.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need isto:1) start a counter at the beginining of the dynamic generation2) append the counter value to the end of a string variable name3) DECLARE the new variable and attach that loop cycle of text to it,or attach each chunk of characters < 80004) build the EXEC() string by concatenating each dynamic varibleCan this be done? Should it be done? Is there a better way to addressthis type of problem?Thanks for any ideas or insightsTim Pascoe
View Replies !
Need To Declare Multiple Values
Alright, so I have this problem. I want to make it easy for me andothers to be able to run a query and easily choose whether we wantMerchants or NonMerchants. Previously, we would have to comment outbits of code and would leave things messy (it would also leave room forerror). So, I'm thought DECLARE and SET would work. Wrong.This is what I have....DECLARE @Merchant VARCHARSET @Merchant = (Select CONVERT(VARCHAR, Id) + ','FROM AdminAdvertiserTypesDDLWHERE Id IN (1,3,4,5)) // Includes Active, Out of Business, Cease to do business, InactiveI've also tried...SET @Merchant = '1,3,4,5'Then, in the query itself I try:WHERE AdminAdvertiserTypesDDL.Id = @MerchantorWHERE AdminAdvertiserTypesDDL.Id IN @MerchantorWHERE AdminAdvertiserTypesDDL.Id IN (@Merchant)orWHERE AdminAdvertiserTypesDDL.Id LIKE @MerchantEither way, it will ONLY show me the merchants whose Id is 1. When Imake the query:WHERE AdminAdvertiserTypesDDL.Id IN (1,3,4,5)I finally get the desired results.Any ideas or tips?Thank you so much!
View Replies !
SELECT DISTINCT In DECLARE
Hi I try to use this code in query analyzer 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? Best regards
View Replies !
How Can I Create View Within Declare ?
dear all how can i create view within declare ? if i had one script contains declare if it is possible to combine both table with union ? declare @a varchar(20) select @a = ptraceno from phonelevel where(ptraceno = '0913787170' or otherno = '0913787170') select * from phonelevel where ptraceno = @a declare @b varchar(20) select @b = ptraceno from phonelevel where(ptraceno = '0913011676' or otherno = '0913011676') select * from phonelevel where ptraceno = @b
View Replies !
Syntax Error On Declare.
I am trying to create a view or a table with in SQL system and it keeps coming up with incorrect syntax near the word declare. It runs fine as long as I do not try and create a table or a view, which unfortunatly I need to do as I have to export the data into Excel for the finance guys. The syntax is below.... Create View dbo.Z_Cashflow_Forc_Paymsdue As Declare @Firstday Int Declare @Nextday Int Set @Firstday = '10' Set @Nextday = '25' Select [Supplier Code], [Supplier Name], Stype, [Order No], [Line No], [Due Date], [Mat Part], [Qty Ordered], [Purch Price], [Value], Dateadd(dd,Screditdays,[Due Date]) as [Paym Date], Letype, Lecurcode, Case When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))) End AS [Paymdate], Case When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))) End AS [PaymWk], Case When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))) End AS [PaymYr] from Z_Cashflow_Purchorders, Wsuppliers Where [Supplier Code] = Supp Union Select [Supplier Code], [Supplier Name], Stype, [Order No], [Line No], [Due Date], [Mat Part], [Qty Ordered], [Purch Price], [Value], Dateadd(dd,Screditdays,[Due Date]) as [Paym Date], Letype, Lecurcode, Case When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))) End AS [Paymdate], Case When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))) End AS [PaymWk], Case When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))) When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))) End AS [PaymYr] from Z_Cashflow_Schedorders, Wsuppliers Where [Supplier Code] = Supp
View Replies !
Must Declare The Scalar Variable
create PROCEDURE [Update_Purged] AS Declare @Stg_Purged_union table (PurgedAccountUnionID int IDENTITY, Coid char(50), FacilityId int, AccountID int, PatientName char(50), PatientNo char(50), PT char(50), ST char(50), PurgeDt datetime); BEGIN INSERT INTO @Stg_Purged_Union ([Coid] ,null ,null ,[PatientName] ,[PatientNo] ,[PT] , ,[PurgeDt]) select coid, patientname, patientno, pt, st, purgedt from [Stg_PurgedAccount] --updating the facilityid in @stg_purged_account_file_union update @stg_purged_union set facilityid=(select facilityid from appfacility as b where b.unitnum=(select unitnum from appfacility as c where c.unitnum=b.unitnum) and @Stg_Purged_Union.coid=b.unitnum) I am getting the following error. Must declare the scalar variable "@Stg_Purged_Union"
View Replies !
Declare Variables In A SP Dynamic?
Hello there, i have been asked about a thing that, i think, is not possible. But maybe i am wrong. question: Is it possible to have a Stored Procedure in that the declaration of the variables is dynamic? This means, can i get the variable name and the type of it from a database to create a dynamic stored procedure that changes itself by firing a trigger. Thanks for all oppinions and answers. everWantedLINUX
View Replies !
Declare Variable Error
Can anyone tell me why I keep getting this error? I am declaring the variable, but it's not recognizing it? What am I missing? ------error--------------- Server: Msg 137, Level 15, State 2, Procedure sp_CopyData, Line 85 Must declare the variable '@DatabaseFrom'. -----sp---- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create Procedure dbo.sp_CopyData (@ClientAbbrev nchar(4) ) AS DECLARE @DatabaseFrom varchar(100) Set @DatabaseFrom = @ClientAbbrev + '.dbo.tsn_ClaimStatus' -------------------------------------------------------------- delete from sherrisplayground.dbo.tsn_ClaimStatus where csclientcode = @ClientAbbrev ---Insert Data from Original table into copied table--------- Insert into [AO3AO3].sherrisplayground.dbo.tsn_ClaimStatus ( CsClientCode, ClaimStatusID, Pat, Claim, [ID], Code, [Date], ActionID, Comment2, Comment3, Comment4, [Followup Date], Checkamt, UserName) select @ClientAbbrev, ClaimStatusID, Pat, Claim, [ID], Code, [Date], ActionID, Comment2, Comment3, Comment4, [Followup Date], Checkamt, UserName from @DatabaseFrom return GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
View Replies !
Using A Variable In SQL Script (Declare @x)
I am not sure why the following does not work... I am declaring a variable to hold a string to be used in my script. The contents of the variable looks perfect and works independently, the variable it does not work. (BTW, do you call this a variable in T-SQL too?). The idea is to pass the value to the script to generate different results sets. //Parameter DECLARE @Codes varchar(8000); SET @Codes = '''07-1110_CHA,1'',''07-1110_DCV,2'''; //Examine contents - results: '07-1110_CHA,1','07-1110_DCV,2' Select @Codes; //Works fine Select screening_cd,check_amount From accounting Where screening_cd in ('07-1110_CHA,1','07-1110_DCV,2'); //This does not work Select screening_cd,check_amount From accounting Where screening_cd in (@Codes); TIA
View Replies !
Error 137: Must Declare Variable ...
Hello, I have the following SP, which gives out a "Error 137: Must declare variable @tmp_return_tbl" error. This is the important part of the code: . . . -- DECLARE TABLE VARIABLE DECLARE @tmp_return_tbl TABLE (tID int, Text_Title nvarchar(30), Text_Body nvarchar(100)) -- fill out table variable USING A SELECT FROM ANOTHER TABLE VARIABLE -- NO PROBLEM HERE INSERT INTO @tmp_return_tbl SELECT TOP 1 * FROM @tmp_tbl ORDER BY NEWID() -- TRYING TO UPDATE A TABLE UPDATE xTable SET xTable.fieldY = xTable.fieldY + 1 WHERE xTable.tID = @tmp_return_tbl.tID --THIS PRODUCES THE ERROR . . . I know I cannot use a table variable in a JOIN without using an Alias, or use it directly in dynamic SQL (different scope) - but is this the problem here? What am I doing wrong? Your help is much appreciated.
View Replies !
Declare Un-static Decimal Value
I try to declare a variable with un static decimal point in the following statement:- declare @mpt int; declare @mpq nvarchar (1) SELECT @mpq = (SELECT mpq FROM ims.parm) exec('declare @MM1 decimal (10,'+ @mpq +');') set @mm1 = 1 print @mpt when i print the declare statement looks like the following: declare @MM1 decimal (10,2); but with exec the statement, I have the following error:- Msg 137, Level 15, State 1, Line 18 Must declare the scalar variable "@mm1". Best regards
View Replies !
Must Declare Scalar Variable @ID
the following is my code can anybody rectify my problem that i get when running my application "Must declare scalar variable @ID" <asp:GridView ID="GridView1" DataKeyNames="ID" runat="server" AutoGenerateColumns="False" BackImageUrl="~/App_Themes/SkinFile/back1.jpg" BorderColor="Teal" BorderStyle="Solid" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ButtonType="Button" ShowSelectButton="True" /> <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" /> <asp:BoundField DataField="Answers" HeaderText="Answers" SortExpression="Answers" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:YahooConnectionString7 %>" DeleteCommand="DELETE FROM Answers WHERE (ID = @ID)" InsertCommand="INSERT INTO Answers(ID, Answers) VALUES (@ID, @Answers)" SelectCommand="SELECT Answers.* FROM Answers" UpdateCommand="UPDATE Answers SET ID = @ID, Answers = @Answers WHERE (ID = @ID)"> <DeleteParameters> <asp:Parameter Name="ID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ID" /> <asp:Parameter Name="Answers" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ID" /> <asp:Parameter Name="Answers" /> </InsertParameters> </asp:SqlDataSource>
View Replies !
|
TRACKER
