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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
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 Replies !
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 !
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 !
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 !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Using A Cursor As A Function Parameter
I've created a function that converts the rows of a column into a delimited string using a passed cursor and delimiter character. In the past I did this in Oracle and called it as shown in the following example: SELECT Table1.ID, Table1.FirstName, Table1.LastName, fnDelimitRows(CURSOR(SELECT Table2.CourseName FROM Table2 WHERE Table2.StudentID = Table1.ID), ',') AS AssignedCourses FROM Table1 23 John Smith CS101,MT200,BIO100 43 Julio Johnson CS200,ENG100,MT300 How would I pass a cursor into a function in SS like I did above in Oracle? Thanks!
View Replies !
Using Cursor As OUT Parameter In Stored Procedure
Hi guys, I have a serious problem. I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures : create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes; create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val; How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike
View Replies !
Passing A Parameter To A USE Command Within A Cursor
Hi everyone I am trying to pass a parameter to a USE command within the body of a cursor. I keep getting an error. Here is my code. Code Snippet DECLARE @SQLText AS NVARCHAR(4000) Declare @SQLText2 as NVARCHAR (2000) DECLARE Cursor_Trial CURSOR FOR SELECT name FROM sys.sysdatabases order by name Declare @name as varchar(255) Open Cursor_Trial FETCH NEXT FROM Cursor_Trial INTO @name WHILE @@FETCH_STATUS = 0 BEGIN Use @name --------This is where I'm getting the error Set @SQLText2 = @name select @SQLText2, * from sys.database_principals where type_desc='DATABASE_ROLE' and name = 'xxxxx' FETCH NEXT FROM Cursor_Trial INTO @name END CLOSE Cursor_Trial DEALLOCATE Cursor_Trial As you can see I am trying to pass the next database name into the body of the cursor so that the query will pull the role information only for the current database in the loop. Any thoughts or tips? Is there a better way to accomplish what I'm trying to do here?
View Replies !
Need Help With A SQL Statement - Trying Not To Use A Cursor
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View Replies !
Using Select Statement Instead Of Cursor
Hi All, Can anyone please help? TableA has data as below: ssn sex dob rel_code 111111111 m 19500403 m 111111111 f 19570908 w 111111111 f 19770804 d 111111111 f 19801203 d 111111111 f 19869712 d 111111111 m 19870907 s 111111111 m 19901211 s I have to convert the rel_code into a specific manner so the data will look as below in TableB: ssn sex dob rel_code 111111111 m 19500403 01 111111111 f 19570908 02 111111111 f 19770804 20 111111111 f 19801203 21 111111111 f 19869712 22 111111111 m 19870907 30 111111111 m 19901211 31 Member's rel_code = 01 spouse's rel_code = 02 daughter's rel_code starts from 20 with the oldest and increments by 1. Son's rel_code starts from 30 and increments by 1 from oldest to the youngest. I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor. Thanks in advance. Jannat.
View Replies !
Select Statement In Cursor
Hi... I have a stored procedure that rertrieves data from an sql database and sends out a mail to each receipient who meets the criteria I am using SQL mail. I dynamically generate the where clause for my sql query based on criteria taken from other stored procedures and store it in a varchar variable called @sqlquery When i have the following code to run my cursor DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary. What should i do. i have tested the variable @sqlquery and it is definately not blank. There is no bracket error or anything. Please help!!! Thanks much indeed Ramesh
View Replies !
Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem on the select statement in sql cursor My select statement is stored in 2 variables one holds the select clause and the other holds the where clause I am doing a small test as my seelct statement is very complicated lots of joins and it is built up from lots of parameters from other queries and from another stored procedure as well Hope you can help when i type the following code: declare @query varchar(100) declare @query2 varchar(100) set @query = "SELECT FROM ml_testMaillist " set @query2 = " WHERE m_Email= 'ramesh@go-events.com' " DECLARE overdue2 CURSOR LOCAL FORWARD_ONLY exec(@query + @query2) open overdue2 I get the error Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'exec'. Please please help as this is very impt to me Thanks Thanks Regards
View Replies !
How To Capture The Value For A CURSOR Statement
Hi everyone, The following snippet of code returns something like that: string;string1;string2 Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task) Thanks a lot, declare @anex as varchar(500) declare @anex2 as varchar(700) set @anex2 = '' DECLARE anexos CURSOR FOR SELECT [Ruta] + [Fichero] as ANEXO FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO WHERE ENVIADO = 0 OPEN anexos; FETCH NEXT FROM anexos INTO @anex WHILE @@FETCH_STATUS = 0 BEGIN IF @anex2 = '' begin set @anex2 = @anex end else begin set @anex2 = @anex2 + ';' + @anex end FETCH NEXT FROM anexos INTO @anex END CLOSE anexos DEALLOCATE anexos
View Replies !
Help With Cursor And Fetch Statement
Hello, I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function. I have a temp table populated with the below headers and their associated data. The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost. The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function. One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand. The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with. The current script I have written is below. If you can offer any help I would greatly appreciate it. Code SnippetDeclare @ItemID VarChar(30), @QtyOnHand Decimal (16,8), @WhseID VarChar (6), @SumRcvd Int, @TranID VarChar(30), @TranDate DateTime, @QtyRcvd Decimal (16,8), @UnitCost Decimal (16,8), @ItemKey Int, @WhseKey Int, @ShortDesc VarChar (40), @StdCost Decimal (16,8) DECLARE Temp_cursor CURSOR FOR SELECT TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost FROM #Temp1 tem OPEN Temp_cursor FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost WHILE @@FETCH_STATUS = 0 BEGIN -- 0 Insert Into #Temp3 (TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost) Values (@TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost) FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost
View Replies !
One Statement Update - Join, No Cursor ?
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View Replies !
How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone, I have been trying to perform the following task: Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns: Database name Table name Column name from that table with a clustered index I have already created the following script which displays all the databases for a given server: declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name open testCursorForDB fetch next from testCursorForDB into @DBname while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end close testCursorForDB deallocate testCursorForDB I also have created the following query which will display all the table and column names which have a clustered index for a given database: select object_name(i.id) as TableName, i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1' However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor. Any help is greatly appreciated Thanks!
View Replies !
[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions) Code: declare @sql char(100) set @sql = 'select ma_kh, ten from _khang' Now, I want to create a cursor to recalculate some values I've tried: Code: declare cur_T cursor for exec(@sql) open cur_T but it doesn't work. Can I have another way to do that???
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
24000 Invalid Cursor State. Prepared Statement
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++. I am not using / importing any dlls. I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery. I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed. First time when I bind paramaters using SQLBindParameter it works perfect. When loop gets executed secondtime onwards, it gives an error. SQLState: 24000 [ODBC Client Interface]Invalid cursor state. If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error. I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time. Have I missed any step? Where & when I should code the cursor type? Any specific libraries I need to link? Thanks
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor. Here is my query sample for yours to understand. SET NOCOUNT ON DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000) Set @qstring = 'select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm' PRINT '-------- ITEM Products Report --------' DECLARE ITEM_cursor CURSOR FOR execute (@qstring) OPEN ITEM_cursor FETCH NEXT FROM ITEM_cursor INTO @ITEMCODE WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' SELECT @message = '----- Products From ITEM: ' + @ITEMNAME PRINT @message -- Get the next ITEM. FETCH NEXT FROM ITEM_cursor INTO @ITEMcode END CLOSE ITEM_cursor DEALLOCATE ITEM_cursor Why i use @qstring? It is because the query will be changed by different critiera. Regards Edmund
View Replies !
SQL Parameter And LIKE % % Statement
Hi there, I am trying to use and SQL statement with a LIKE query with % at the begining and end of the parameter to get any combination of the search parameter i.e. last name. My piece of SQL looks like this: WHERE userUsername=@Username AND contactName LIKE @SearchTerm I need something like this: WHERE userUsername=@Username AND contactName LIKE %@SearchTerm% However this does not work. Any suggestions would be greatly appreciaed. Regards, TCM_
View Replies !
In Statement With Parameter
Hi, I have a query that uses a parameter as criteria e.g. type = :_type (i set p_type = 'Trad') I want to change this parameter to be a range of values. However when I change the query to be type in (:_type) I get nothing returned if I set p_type to be 'Trad,New' but I do get something if it's just Trad. Can anybody advise on the syntax? I've tried a number of variations.
View Replies !
Help With Date Parameter And If/else Statement
Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George
View Replies !
Passing Parameter To Sql Statement
I am trying to pass a parameter to an sql statement that executes a stored proc. Here is my sql statement from the Execute SQL Task component. USE [master] GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_sendSMTPmail] @To = N'john.r.carter@bankofamerica.com', @Subject = ?, @Body = NULL, @Importance = NULL, @Cc = NULL, @Bcc = NULL, @Attachments = NULL, @HTMLFormat = NULL, @From = N'bpm@bankofamerica.com' SELECT 'Return Value' = @return_value GO I have the ByPassPrepare set to True. Can anyone tell me why I can't get this to work. Thanks, JC
View Replies !
Passing Parameter To IN Statement
Hi, I try to pass a parameter to a IN statement in SSRS but couldn't get it work. It likes following sample: select * from T1 where name IN (@nameList) I passed @nameList like 'aa', 'bb', ''aa'', ''bb''(two single quotations), all didn't work. I couldn't even get it work on "Reporting -> Data" screen. What is the right way to pass a parameter to IN statement in SSRS? I am using C# and SSRS2005. Thanks!
View Replies !
Multi Parameter AND Statement
Is it possible for an AND statement to take mulitple parameters? See below code: SELECT tblQuestion.Question, tblAnswer.Answer FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID) WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14) Is something like this possible?: AND (tblAnswer.QuestionID = 14, 26) Thanks
View Replies !
Remove Parameter In A Select Statement
I have a select statement that I am using and wanted to know if there is a way to remove a parameter on the fly. What I want to do is remove the @status if the text = "". so it will only search by the date and endate is that possible to do. Here is my code.
View Replies !
Parsing A SQL Statement As A Parameter And Executing It
Hi all,I was just wondering if it could be possible to excecute a statementwhich is extracted from variabele, Example:declare @SqlStatement varchar (100)set @SqlStatement = 'select * from company'the @SqlStatement contains the actual sql query, is it possible somehow to straight away convert it into excecution?I've tried:exec @SqlStatementbut exec or execute are meant to be for proceduresAny idea?
View Replies !
Pass Column Name Using Parameter In SQL Statement...
Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...
View Replies !
Openrowset With Parameter In Exec Statement?
Hi! Is it possible to call proc with parameter in openrowset?: declare @str varchar(100); select @str = 'EXEC SP_HELPTEXT ''createReport'''; select * from OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str) AS a; Incorrect syntax near '@str'. ------------------ select * from OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC SP_HELPTEXT ''createReport''') AS a; Msg 7357, Level 16, State 2, Line 1 Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object. Best regards B. D. Jensen
View Replies !
Can I Create A 'Top N' Statement Within A Stored Procedure Using A Parameter?
In a 'Top n' type statement I wish to be able to insert the n valuefrom a parameter, within a stored precedure egHaving declared @pageSize as a parameter I want to run the followingtype of query :SELECT DISTINCT TOP @pageSize routeID, routeName FROMtblRoute_HeaderWhen I attempt to do so I get an error mesage indicating incorrectsyntax. I do not get an error message if I specify 'n' directly as inTOP 10Am I missing something or is this not possible within a storedprocedure?Best wishes, John Morgan
View Replies !
Case Statement On Where Clause If Parameter =NULL
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right: SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr FROM dbo.Engr_BOM_Control bom WITH (nolock) INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock) ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID CASE WHEN @v_Bill_Type IS NOT NULL THEN AND bom.Bill_Type=@v_Bill_Type END
View Replies !
Prepared Statement ......... Expects Parameter @CORP_NAME, Which Was Not Supplied
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me... private void Submit1_ServerClick(object sender, System.EventArgs e) { erkaner = Page.Session.Contents["CORP_ID"].ToString(); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox32.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox27.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TC_NO", System.Data.SqlDbType.VarChar, 50, TextBox28.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ID", System.Data.SqlDbType.Int, 4, erkaner));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_EMAIL", System.Data.SqlDbType.VarChar, 50, TextBox29.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TEL", System.Data.SqlDbType.VarChar, 50, TextBox30.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_DUTY", System.Data.SqlDbType.VarChar, 150, TextBox31.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FOUND_YEAR", System.Data.SqlDbType.VarChar, 50, TextBox33.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COUNTRY", System.Data.SqlDbType.VarChar, 50, DropDownListUlke.SelectedValue.ToString())); if (DropDownListUlke.SelectedIndex == 148) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, DropdownlistSehir.SelectedValue.ToString()));else if(DropDownListUlke.SelectedIndex != 0 && DropDownListUlke.SelectedIndex != 148) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, TextBox1.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SECTOR", System.Data.SqlDbType.VarChar, 150, RadioButtonList6.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMP_NUM", System.Data.SqlDbType.VarChar, 50, RadioButtonList1.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ACT", System.Data.SqlDbType.VarChar, 150, DropDownList3.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PC_NUM", System.Data.SqlDbType.VarChar, 50, TextBox35.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_USER_NUM", System.Data.SqlDbType.VarChar, 50, "INT_USER_NUM")); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_LAN", System.Data.SqlDbType.VarChar, 50, RadioButtonList3.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_SERVER", System.Data.SqlDbType.VarChar, 50, RadioButtonList4.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ECOM_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox37.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OT_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox38.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_CON_TYPE", System.Data.SqlDbType.VarChar, 50, RadioButtonList5.SelectedValue.ToString())); if (CheckBox1.Checked) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "Ticari Uygulamalar (B2B,B2C,e-iş,e-ihracat,…)"));else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox2.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "Yönetişim Uygulamaları (Muhasebe, stok, satış, kalite, raporlama,denetim...)")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox3.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "ERP Uygulamalari(Unity, SAP, JDE, BAAN, QAD vb.)")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox4.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "Müşteri İlişkileri Yönetimi (CRM) Uygulamaları")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox5.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "Bilgisayar Destekli Tasarım / Bilgisayar Destekli Üretim (CAD/CAM) Uygulamaları")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox6.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "Veri Ambarı ve Veri Madenciliği Uygulamaları ")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox7.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, TextBox36.Text)); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, "")); sqlUpdateCommand1.CommandText = "UPDATE dbo.CORP_TBL SET REP_NAME = @REP_NAME, CORP_NAME = @CORP_NAME, REP_TC_NO =" + " @REP_TC_NO, REP_EMAIL = @REP_EMAIL, REP_TEL = @REP_TEL, REP_DUTY = @REP_DUTY, F" + "OUND_YEAR = @FOUND_YEAR, COUNTRY = @COUNTRY, CITY = @CITY, SECTOR = @SECTOR, EMP" + "_NUM = @EMP_NUM, CORP_ACT = @CORP_ACT, PC_NUM = @PC_NUM, INT_USER_NUM = @INT_USE" + "R_NUM, HAVE_LAN = @HAVE_LAN, HAVE_SERVER = @HAVE_SERVER, ECOM_SER_NUM = @ECOM_SE" + "R_NUM, OT_SER_NUM = @OT_SER_NUM, INT_CON_TYPE = @INT_CON_TYPE, APPLICATION1 = @A" + "PPLICATION1, APPLICATION2 = @APPLICATION2, APPLICATION3 = @APPLICATION3, APPLICA" + "TION4 = @APPLICATION4, APPLICATION5 = @APPLICATION5, APPLICATION6 = @APPLICATION" + "6, APPLICATION7 = @APPLICATION7 WHERE CORP_ID = @CORP_ID"; sqlUpdateCommand1.Connection = sqlConnection1; sqlUpdateCommand1.Connection.Open(); sqlUpdateCommand1.ExecuteNonQuery(); //This line gives the errorstring mySqlQuery = "UPDATE CORP_TBL SET FLAG = 1 WHERE CORP_ID=" + Page.Session.Contents["CORP_ID"].ToString();SqlCommand myCommand = new SqlCommand(mySqlQuery, sqlConnection1); myCommand.ExecuteNonQuery(); Page.Response.Redirect("main.aspx"); }
View Replies !
How Do I Assign A String To A Parameter I'm Passing To A Select Statement?
Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so: WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.
View Replies !
Unable To Pass Queue Name In A Parameter For The FROM Clause Of RECEIVE Statement
I'm writing some generalized stored procedures for use form managed code. The following stored proc works great: CREATE PROCEDURE fnd_Send ( @Message VARCHAR(5000), @Contract SYSNAME, @MessageType SYSNAME, @FromService SYSNAME, @ToService VARCHAR(100), @Encrypted Bit ) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @Handle FROM SERVICE @FromService TO SERVICE @ToService ON CONTRACT @Contract WITH ENCRYPTION = OFF; SEND ON CONVERSATION @Handle MESSAGE TYPE @MessageType(@Message); Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages. The following will not compile: CREATE PROCEDURE fnd_Receive ( @Queue SYSNAME, @Message VARCHAR(5000) out, @MessageType SYSNAME out ) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER; RECEIVE TOP (1) @Handle = conversation_handle, @MessageType = message_type_name, @Message = message_body FROM @Queue; END GO It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly: Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15 Incorrect syntax near '@Queue'. It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to. Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement. Thank you in advance - Jeff Odell Catapult Systems
View Replies !
Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
View Replies !
Variable In CURSOR Sql Statement (was &"Please Help Me&")
Hi All, What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also). ============================================= SET @Where = '' if IsNull(@Input1,NULL) <> NULL Set @Where = @Where + " AND studentid='" + @input1 +"'" if isnull(@Input2,NULL) <> NULL Set @Where = @Where + " AND teacherid =' " + @Input2 +"'" DECLARE curs1 CURSOR SCROLL FOR SELECT firstname FROM school WHERE school ='ABC' + @where ============================================= Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ? Thanks !
View Replies !
|
TRACKER
