Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot. CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS declare @cmdtxt as varchar(4000), @SQLString NVARCHAR(4000), @SQLString1 NVARCHAR(4000), @pid varchar(22), @lname varchar(60) begin EXEC SQL BEGIN DECLARE SECTION; char prep[] = @p_query; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE prep_stat FROM :prep; EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat; OPEN contact_crsr FETCH NEXT FROM contact_crsr INTO @pid, @lname -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt" exec master..xp_cmdshell @cmdtxt FETCH NEXT FROM contact_crsr INTO @pid, @lname END CLOSE contact_crsr DEALLOCATE contact_crsr end
View Replies !
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source? Something like this: CREATE PROCEDURE TestHardDisk AS BEGIN DECLARE CURSOR HardDisk_Cursor FOR Exec xp_FixedDrives -- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source OPEN CURSOR HardDisk_Cursor FETCH NEXT FROM HardDisk_Cursor INTO @Drive, @Space WHILE @@FETCH_STATUS = 0 BEGIN ... END END
View Replies !
Make A Call To AS/400 Stored Procedure From DTS
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View Replies !
Make The Stored Procedure To Run Daily
Hi, I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible. Thanks in advance for any help. Regards, Sangeetha
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 !
Stored Procedure To Make Backup Of Database...
Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well. Thanks...
View Replies !
I Need To Make This Stored Procedure 2005 Compatible
Hello. I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do. Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated! -------------------------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER Procedure aes.Check_Dependent_Rows_Exist (@RowID int, @has_rows int OUTPUT ) AS BEGIN DECLARE @Colname varchar(200), @Tablename varchar(200) DECLARE @cnt int DECLARE @temp_row int DECLARE @owner varchar(25) DECLARE @ownerid int DECLARE @lstrSql nvarchar(2000) -- #1: declare cursor for maximum performance DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName FROM sysreferences INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid WHERE OBJECT_NAME(rkeyid)= 'customer' OPEN lcur CREATE TABLE #Temp (DependentRows int) -- #2: only return a bit indicating if dependant rows exist or not SET @has_rows = 0 FETCH NEXT FROM lcur INTO @Colname,@Tablename WHILE @@FETCH_STATUS = 0 BEGIN SET @temp_row = 0 SELECT @ownerid = uid from sysobjects where name = @Tablename SELECT @owner = [name] from sysusers where uid = @ownerid SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' + @Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + '' --print @lstrSql EXEC (@lstrSql) SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp IF @temp_row > 0 BEGIN -- #3: stop processing as soon as dependant rows are found to exist SET @has_rows = 1 BREAK END FETCH NEXT FROM lcur INTO @Colname,@TableName END deallocate lcur END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ----------------------------------------------------------------------- error Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.order_detail'. Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.invoice_header'. Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.order_header'. Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.payment'. Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.cash_on_account'. (1 row(s) affected) Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
View Replies !
Cursor With Stored Procedure
I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie: CREATE PROCEDURE [dbo].[bsa_GetImportIDs] (@FilterText varchar(1000)) AS DECLARE @MySQL varchar(1000) SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText EXEC (@MySQL) GO Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie: CREATE PROCEDURE [dbo].[bsa_PutLargeCase] AS DECLARE @CaseID uniqueidentifier SET @CaseID = NewID() Declare @ImportID uniqueidentifier Declare curClient Cursor FAST_FORWARD for SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!! Open curClient FETCH NEXT FROM curClient INTO @ImportID WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID FETCH NEXT FROM curClient INTO @ImportID END CLOSE curClient DEALLOCATE curClient GO How can I utilize my first stored procedure in the cursor of the second? ... or Are there any other approaches that may be a better solution to what I am trying to accomplish? Thanks in advance for any input.
View Replies !
Stored Procedure Into A Cursor
Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance
View Replies !
Help In Stored Procedure With Cursor
I am trying to create the procedure from two tables to create the flat file from the two tables and as they are processed to the file then want to change the status from 'U' to 'P'. I am trying to declare the cursor from the union of two tables so that I can store the data into one table. And then put the data into the flat file and then marked the processed data as 'P' from 'U' please help in doing that. thanks, Harish CREATE PROCEDURE SP_P8 AS declare c123 cursor for select 'CA'+'|'+COMPANY+'|'+COMPANYNAME+'|'+CUSTOMERNUMBE R+'|'+COMPANYPHONE +'|'+COMPANYFAX+'|'+FEDTAXID+'|'+DUNSNUMBER+'|'+S1 099+'|'+DUNSSUFFIX+'|'+convert(char,ADDRESSSEQ) +'|'+COUNTRY+'|'+ADDRESSTYPE+'|'+ADDRESS1+'|'+ADDR ESS2+'|'+ADDRESS3+'|'+ADDRESS4 +'|'+CITY+'|'+STATE+'|'+ZIP+'|'+FAXNUMBER hello1,status from v1 UNION select 'CT'+'|'+COMPANY+'|'+CONTACTNAME+'|'+CONTACTTYPE+' |'+CONVERT(CHAR(10),CONTACTADDRESS) +'|'+PHONE+'|'+FAX+'|'+TITLE hello,status from v3 declare @status char(1) declare @wholerecord text open c123 fetch next from c123 into @wholerecord,@status while @@fetch_status=0 begin update v1 set status='P' where current of c123 update v3 set status='P' where current of c123 fetch next from c123 into @wholerecord,@status end close c123 deallocate c123 select * into temp23 from v2 where status='P' and declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XCUST'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..temp23 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Ppassword' exec master..xp_cmdshell @cmdstring drop table temp23
View Replies !
Help In Stored Procedure With Cursor
Please help in creating the stored procedure for the following code. Thanks in Advance. ============================= Create procedure extractdata @companynumber varchar(3), @firstdateofmonth int, @lastdateofmonth int as truncate table xt truncate table xtt insert into xt(account_code,balance_date,net_change,current_ba lance,oldlob) select account_code, balance_date,net_change,current_balance,@companynu mber from ccc@companynumber..glbal where balance_date<=@firstdateofmonth declare @v1 varchar(32) declare @v2 int declare @v3 float declare c1 cursor for select account_code, max(balance_date) from xt where oldlob=@companynumber group by account_code open c1 fetch next from c1 into @v1,@v2 WHILE @@FETCH_STATUS = 0 begin update xt set status='max' where account_code=@v1 and balance_date=@v2 and oldlob=@companynumber fetch next from c1 into @v1,@v2 end close c1 deallocate c1 insert into xtt(account_code,balance_date,net_change,current_b alance,oldlob) select account_code,balance_date,net_change,current_balan ce,oldlob from xt where status='max' and oldlob=@companynumber order by account_code insert into test1(account_code,current_balance,net_change,bala nce_date,oldlob) select account_code,round(current_balance,2),round(net_ch ange,2),balance_date,@companynumber from xtt where oldlob=@companynumber and account_code in (select account_code from xxxmay where oldlob=@companynumber) update test1 set net_change=0 where balance_date<@lastdateofmonth and oldlob=@companynumber select sum(current_balance),sum(net_change) from test1 where oldlob=@companynumber
View Replies !
Out A Cursor From A Stored Procedure
hello! any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor. please help! honey
View Replies !
Mssql 2005. How To Make Update Stored Procedure ?
Hi ~ I made simple stored procedure that is to update user information following as... ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) ) AS UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message. What's the problem ?
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 !
Creating Cursor From Stored Procedure
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky
View Replies !
Error From Stored Procedure With A Cursor
I receive the following intermittent error when executing a stored procedure: Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000] Msg 3624, Sev 16: Location: lckclass.cpp:111 Expression: m_status == ACTIVE || m_status == ORPHANED SPID: 17 Process ID: 203 [SQLSTATE 01000] The process uses a cursor to update a SQL7 table from another. This is not a consistent failure. Sometimes the procedure runs fine to completion. Has anyone else had the same problem??? We had a suggestion that it might be a tempdb problem??? Any ideas?
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 !
Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor. The doctors table has the following columns with specialism allowing NULL values doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) ) Any help would be greatly appreciated.
View Replies !
How To Make A Program Quit In The Middle Of The Trigger (stored Procedure)?
Hi, i made a trigger and takes 2 columns combine the value and place on the third columns on insert. Everything has been doing fine until recently, the policy has changed: those 2 columns are now allowed NULL. This trigger will bomb if either or both of those 2 columns are null, so I need some ways to bail in the middle of the trigger. Please show me what the correct sytax I need in order to escacpe from a stored procedure? thank you. p.s: i'm thinking it will be alone the line as this: if @firstCol = null return end if @secondCol = null return end
View Replies !
Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD FOR select fldSoNo from #tblSalesOrders declare @newSONO1 int OPEN @CursorOrders FETCH NEXT FROM @CursorOrders INTO @soNo1 WHILE @@FETCH_STATUS = 0 BEGIN ----for each salesorder insert to salesorderline insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1; set @newSONO1=SCOPE_IDENTITY; -------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert -------------------------- SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM FETCH NEXT FROM @CursorOrders INTO @soNo1 END CLOSE @CursorOrders DEALLOCATE @CursorOrders
View Replies !
Receiving And Sending A Cursor With(in) A Stored Procedure
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record. And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed. Thanks for help on this, Peter
View Replies !
Unique Cursor Name In Recursive Stored Procedure
How can I solve this probleM? I thing that I need to create cursor with unique cursor name procedure XX param DECLARE cc CURSOR FOR SELECT....param --ERROR bcause it's already exists OPEN cc ... WHILE @@FETCH_STATUS = 0 BEGIN XX param FETCH NEXT ... END CLOSE cc ....
View Replies !
Stored Procedure Cursor Problem URGENT
Hi, I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks CREATE PROCEDURE [MYSP] AS Declare @pdesc nvarchar(30) Declare @ssc int Declare @myid int Declare @name nvarchar(30) Declare new_cur CURSOR DYNAMIC FOR SELECT ProductDescription, SubSubCatID FROM C2000HPB FOR UPDATE Open new_cur FETCH FROM new_cur INTO @pdesc, @ssc While @@FETCH_STATUS = 0 BEGIN Declare new_cur2 CURSOR DYNAMIC FOR SELECT SubSubCatID, SubSubCategory FROM SSC FOR READ ONLY Open new_cur2 FETCH FROM new_cur2 INTO @myid, @name While @@FETCH_STATUS = 0 BEGIN IF PATINDEX ('@name%',@pdesc) = 0 Set @ssc = @myid UPDATE C2000HPB SET SubSubCatID = @ssc FETCH NEXT FROM new_cur2 INTO @myid, @name END Close new_cur2 DEALLOCATE new_Cur2 FETCH NEXT FROM new_cur INTO @pdesc,@ssc END Close new_cur DEALLOCATE new_Cur
View Replies !
How To Call A Stored Procedure In T-SQL And Pass It To A Cursor
Hi, I have a kind of problem. In SQL Server I have a stored procedure ressembling this: Code Block ALTER PROCEDURE procedure1 ( @param int ) SELECT * FROM table WHERE param = @param Now I want to call this procedure and pass it to a cursor. We all know you can do this: Code Block DELCARE cursor1 CURSOR for SELECT * FROM table WHERE param = @param .. , but I want something like this: Code Block DECLARE cursor1 CURSOR for EXEC procedure1 @param Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant. I have also tried something like this: Code Block ALTER PROCEDURE procedure1 ( @param int ) SELECT @test = id FROM table WHERE param = @param RETURN @test ALTER PROCEDURE procedure2 ( @param int ) DECLARE @var varchar(100) EXEC @var = procedure1 @param But then it returns always 0. So is there a way to pass a procedure's select to a cursor? Thanks in advance Stevevil0
View Replies !
Problem When Invoking Stored Procedure With Cursor From Inside .net
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
View Replies !
Cursor Works In Query Analyzer But Not In Stored Procedure
Hi i have a script works in sql query analyzer; declare @id decimal declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID open mycur; Fetch ABSOLUTE 30 from mycur into @id close mycur; deallocate mycur; select @id this script turns me a value. i create a stored procedure from above script and its syntax is ok; CREATE PROCEDURE SELECT_MyRow AS declare @cur cursor declare @RowID decimal set @cur = CURSOR SCROLL for select myRowID from myTable order by myRowID open @cur Fetch ABSOLUTE 30 from @cur into @RowID close @cur deallocate @cur select @RowID GO my c# code using stored procedure is below; Code Snippet try { OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection); cmd.CommandType = CommandType.StoredProcedure; myconnection.Open(); OleDbDataReader reader = cmd.ExecuteReader(); MessageBox.Show(reader.GetName(0));//here fails while (reader.Read()) { MessageBox.Show(reader.GetDecimal(0).ToString()); } reader.Close(); myconnection.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } The code above fails because reader reads no values, error message is "No data exists for the row/column" but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
View Replies !
Multiple Stored Procedure...or 1 Dynamic Procedure?
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values... my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters... if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs... this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application... but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array?? Cheers, Justin
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 !
Dynamic Where In Stored Procedure Help
Hi all, I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample, where field1= coalesce(@parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution? Thanks, James
View Replies !
Help With Dynamic SQL Stored Procedure
I have a stored procedure spGetAccessLogDynamic and when I try to callit I get the following error:Server: Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'S'.I dont know why because I dont have anything refering to storedprocedure 'S'I have ran my SQL String with sample values and it works fine. So Iam presuming that it is some kind of syntax error in my storedprocedure but have tried everything and cant find it!Anyway here is the sample data I am using to call it and my spExec spGetAccessLogDynamic '24', '2005/07/04 00:00:00 AM', '2005/11/0400:00:00 AM', 'TimeAccessed DESC'CREATE PROCEDURE spGetAccessLogDynamic(@PinServiceID varchar (4),@StartDate varchar(40),@EndDate varchar(40),@SortExp varchar (100))AS-- Create a variable @SQL StatementDECLARE @SQLStatement varchar-- Enter the Dynamic SQL statement into the variable @SQLStatementSELECT @SQLStatement = ( 'SELECT A.PinValue,A.TimeAccessed,C.Forename, C.SurnameFROM AccessLog A, Members C, Pins PWHERE P.PinValue = A.PinValue ANDP.MemberID = C.MemberID AND A.PinServiceID= ''' + @PinServiceID + '''AND A.TimeAccessed BETWEEN dbo.func_DateMidnightPrevious( ''' +@StartDate + ''' ) AND dbo.func_DateMidnightNext( ''' + @EndDate+''')GROUP BY A.PinValue,A.TimeAccessed, C.Forename, C.SurnameORDER BY ' + @SortExp)-- Execute the SQL statementEXEC ( @SQLStatement)GOAny help would be very very much appreciated!!!!!!ThanksCaro
View Replies !
Stored Procedure With Dynamic Sql
CREATE PROCEDURE ggg_test_sp @start_date datetime,@end_Date datetime AS SET NOCOUNT ON DECLARE @sqlstmt varchar(1000) SELECT @sqlstmt='SELECT * FROM ggg_emp WHERE date_join BETWEEN ' +CONVERT(varchar(10),@start_date-1,101) + ' AND ' +CONVERT(varchar(10),@end_Date+1,101) SELECT @sqlstmt EXEC (@sqlstmt) GO I want to apply date filter in the above sp with dynamic sql stmt. When i execute the above procedure with date ranges( @start_date=07/06/2004 AND @end_Date= 08/06/2004)i am not getting any result because my @sqlstmt variable has the select stamet SELECT * FROM ggg_emp WHERE date_join BETWEEN 07/06/2004 AND 08/06/2004 BUT it should have the sqlstmt as SELECT * FROM ggg_emp WHERE date_join BETWEEN '07/06/2004' AND '08/06/2004' to produce the required result I know that for the above SP we dont need any dynamic sql but this is just an example. So anyone can help me on this issue. Thanks.
View Replies !
Dynamic SQL Stored Procedure
We are continuing to have issues with a certain stored procedure using dynamic sql. The issue arose when we tried to clean the stored procedure up, and seemed to have zero problems in staging. As soon as we moved it into production, the stored proc caused excessive blocking and completely slowed down our production environment. We immediately rolled back the older version and production is back to normal. After looking at the new procedure I don't understand how it could cause blocking. Any help is much appreciated! Old Proc without issues---- -------- USE [Realist_Prod_1203] GO /****** Object: StoredProcedure [dbo].[USP_GetMatchedMLSRecord] Script Date: 12/04/2007 09:33:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /* ===================== Created By: Sunil/Sudeep 19-11-2003 Description: Does a lookup of MLS Property data for reverse link. This is susceptible to error in that if erroneous data is given to us,it will not find a match. For this reason, commented out the lookup on Suffix and changed the street to use a like clause. Many users are putting the suffix in the street clause and no hits are generated. This hurts performance, but it improves the hit ratio. Usage: exec USP_GetMatchedMLSRecord 61,'3951','','KENSINGWOOD','DR','3951','columbus','OH','43230','39049','600-260368','600-260368-00','6000260368','urlll' Mods: 01/08/2004 - Balawant - Added nullif(), as it was comparing apn numbers with '' (empty space) 02/23/2004 - Balawant - Added or (or State = '') condition for state, zip, city, StreetDirection and Suffix. 11/18/2004 - Sunil Padmanbhan - Added begin-end and modified altapn and parcelid in nullif statment. 04/03/2007 - Shiny - changed to Parameterized query generation 04/03/2007 - Vasan - Removed redundant nullif's and added a limit of 100 records on output 04/03/2007 - Shiny - Removed more Nullif's and changed datatypes for Zip and CountyID to Char to match with table datatypes 04/05/2007 - Vasan - Modified to match resultsets with original procedure ===================== if exists (select 1 from sysobjects where name = 'USP_GetMatchedMLSRecord') drop procedure USP_GetMatchedMLSRecord grant exec on USP_GetMatchedMLSRecord to webuser */ CREATE PROCEDURE [dbo].[USP_GetMatchedMLSRecord] ( @GroupID int, @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP char(50), @FIPS varchar(10), @ApnNumber varchar(50), @AltApn varchar(50), @ParcelId varchar(50), @ReverseLinkURL varchar(200) ) AS DECLARE @CountyID char(6) Select @CountyID=CountyID from ltCounties where FIPS=@FIPS IF (@ApnNumber IS NOT NULL AND @ApnNumber <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID = @GroupID ; ELSE BEGIN IF (@AltApn IS NOT NULL AND @AltApn <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@AltApn AND GroupID=@GroupID)) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @AltApn AND GroupID=@GroupID; ELSE IF (@ParcelId IS NOT NULL AND @ParcelId <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WHERE APNnumber=@ParcelId AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @ParcelId AND GroupID=@GroupID; ELSE BEGIN -- Finalize parameter values IF @ReverseLinkURL IS NULL SET @ReverseLinkURL = ''; IF @StreetName IS NOT NULL AND @StreetName <> '' SET @StreetName = @StreetName + '%'; -- Build up SQL text dynamically, only including filter predicates for those parameters that the user wants -- to search on. DECLARE @sqltext nvarchar(4000) SET @sqltext = 'Select top 100 '''' + @ReverseLinkURL as ''ReverseLinkBaseURL'',MLSNumber,Comment from tblMLSListing WITH (NOLOCK) where ' -- Because of skew and relative few group IDs, you may want to use an inline literal for this one parameter -- to avoid plan sharing across different GroupIDs. Use explicit parameterization for the other parameters. if @GroupID is null set @sqltext = @sqltext + '1=1' --ignore Group_ID if null else SET @sqltext = @sqltext + 'GroupID=' + CONVERT (varchar(30), @GroupID) + ' ' ; --House number is mandatory: IF @HouseNumber IS NOT NULL AND @HouseNumber <> '' SET @sqltext = @sqltext + ' AND HouseNumber=@HouseNumber ' IF @StreetDirection IS NOT NULL AND @StreetDirection <> '' SET @sqltext = @sqltext + ' AND (StreetDirection=@StreetDirection or @StreetDirection='''') ' IF @StreetName IS NOT NULL AND @StreetName <> '' SET @sqltext = @sqltext + ' AND StreetName like @StreetName ' IF @Suffix IS NOT NULL AND @Suffix <> '' SET @sqltext = @sqltext + ' AND (Suffix=@Suffix or Suffix='''') ' --Unit is mandatory: IF @Unit IS NOT NULL AND @Unit <> '' SET @sqltext = @sqltext + ' AND Unit=@Unit ' IF @City IS NOT NULL AND @City <> '' SET @sqltext = @sqltext + ' AND (City=@City or City='''') ' IF @State IS NOT NULL AND @State <> '' SET @sqltext = @sqltext + ' AND (State=@State or State='''') ' IF @ZIP IS NOT NULL AND @ZIP <> '' SET @sqltext = @sqltext + ' AND (ZIP=@ZIP or ZIP='''') ' --CountyId is mandatory: IF @CountyID IS NOT NULL AND @CountyID <> '' SET @sqltext = @sqltext + ' AND CountyID=@CountyID ' -- Execute as an explicitly parameterized query. This will provide plan reuse for any executions of the proc -- that have the same @GroupID and the same combination of non-empty parameters. /*print @sqltext print '@ReverseLinkURL = ' + @ReverseLinkURL print '@HouseNumber = ' + @HouseNumber print '@StreetDirection = ' + @StreetDirection print '@StreetName = ' + @StreetName print '@Suffix = ' + @Suffix print '@Unit = ' + @Unit print '@City = ' + @City print '@State = ' + @State print '@ZIP = ' + @ZIP print ' @CountyID = ' + @CountyID print 'debug: ApnNumber = ' + @ApnNumber*/ EXEC sp_executesql @sqltext, N'@ReverseLinkURL varchar(200), @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP varchar(50), @CountyID varchar(50)', @ReverseLinkURL=@ReverseLinkURL, @HouseNumber=@HouseNumber, @StreetDirection=@StreetDirection, @StreetName=@StreetName, @Suffix=@Suffix, @Unit=@Unit, @City=@City, @State=@State, @ZIP=@ZIP, @CountyID=@CountyID END END New Proc WITH Blocking issues---- -------- /* ===================== Created By: David Barrs 8-13-2002 Description: Returns the properties for given group id Usage: EXEC USP_GetMatchedMLSRecord 1,'8108','','dunn','','','austin','TX','','48453','','','','http://sef.mlxchange.com/reverselink.asp?action=reverselink' Mods: xx/xx/xxxx - who - Description 11/28/2007 - Shiny - Refactored the procedure \\\\\\ ===================== if exists (select 1 from sysobjects where name = 'USP_GetMatchedMLSRecord') drop procedure USP_GetMatchedMLSRecord grant exec on USP_GetMatchedMLSRecord to webuser */ ALTER PROCEDURE [dbo].[USP_GetMatchedMLSRecord] ( @GroupID int, @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP char(50), @FIPS varchar(10), @ApnNumber varchar(50), @AltApn varchar(50), @ParcelId varchar(50), @ReverseLinkURL varchar(200) ) AS DECLARE @sqltext nvarchar(4000), @paramlist nvarchar(4000), @CountyID char(6) Select @CountyID=CountyID from ltCounties where FIPS=@FIPS IF (@ApnNumber IS NOT NULL AND @ApnNumber <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID = @GroupID ; ELSE BEGIN IF (@AltApn IS NOT NULL AND @AltApn <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@AltApn AND GroupID=@GroupID)) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @AltApn AND GroupID=@GroupID; ELSE IF (@ParcelId IS NOT NULL AND @ParcelId <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WHERE APNnumber=@ParcelId AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @ParcelId AND GroupID=@GroupID; ELSE BEGIN -- Finalize parameter values IF @ReverseLinkURL IS NULL SET @ReverseLinkURL = ''; IF @StreetName IS NOT NULL AND @StreetName <> '' SET @StreetName = @StreetName + '%'; -- Build up SQL text dynamically, only including filter predicates for those parameters that the user wants -- to search on. SELECT @sqltext = 'Select top 100 '''' + @ReverseLinkURL as ''ReverseLinkBaseURL'',MLSNumber,Comment from tblMLSListing WITH (NOLOCK) where ' IF @GroupID IS NOT NULL SELECT @sqltext = @sqltext + 'GroupID=' + CONVERT (varchar(30), @GroupID) + ' ' SELECT @sqltext = @sqltext + ' AND HouseNumber=@HouseNumber ' IF @StreetDirection IS NOT NULL SELECT @sqltext = @sqltext + ' AND StreetDirection = @StreetDirection ' IF @StreetName IS NOT NULL SELECT @sqltext = @sqltext + ' AND StreetName LIKE @StreetName + ''%''' IF @Suffix IS NOT NULL SELECT @sqltext = @sqltext + ' AND Suffix = @Suffix' SELECT @sqltext = @sqltext + ' AND Unit=@Unit ' IF @City IS NOT NULL SELECT @sqltext = @sqltext + ' AND City = @City' IF @State IS NOT NULL SELECT @sqltext = @sqltext + ' AND State = @State' IF @ZIP IS NOT NULL SELECT @sqltext = @sqltext + ' AND ZIP = @ZIP' SELECT @sqltext = @sqltext + ' AND CountyID='+ CONVERT (varchar(30), @CountyID)+' ' SELECT @paramlist = ' @GroupID int, @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP char(50), @FIPS varchar(10), @ApnNumber varchar(50), @AltApn varchar(50), @ParcelId varchar(50), @ReverseLinkURL varchar(200)' /* print '@ReverseLinkURL = ' + @ReverseLinkURL print '@HouseNumber = ' + @HouseNumber print '@StreetDirection = ' + @StreetDirection print '@StreetName = ' + @StreetName print '@Suffix = ' + @Suffix print '@Unit = ' + @Unit print '@City = ' + @City print '@State = ' + @State print '@ZIP = ' + @ZIP print '@CountyID = ' + @CountyID print 'debug: ApnNumber = ' + @ApnNumber */ EXEC sp_executesql @sqltext, @paramlist, @GroupID, @HouseNumber, @StreetDirection, @StreetName, @Suffix, @Unit, @City, @State, @ZIP, @FIPS, @ApnNumber, @AltApn, @ParcelId, @ReverseLinkURL END END; Thank You, -D
View Replies !
Dynamic WHERE In Stored Procedure
Can anyone help me with this dumb question? I want to use a stored procedure to bring back a recordset depending if a bit column is set to 1. My table has a number of columns that are of Data Type bit and I want to be able to specify which particular column I'm interested in as a parameter when I call the Stored Procedure. I have set up the Stored Procedure as follows: CREATE PROCEDURE getProducts @param1 varchar(50) AS SELECT ProductID, ProductName FROM dbo.Products WHERE @param1 = '1' GO I'm calling it like this: Dim cmdX, cmdParam, rsX cmdParam = "OnSpecial" set cmdX = Server.CreateObject("ADODB.Command") cmdX.ActiveConnection = conn_STRING cmdX.CommandText = "dbo.getProducts" cmdX.Parameters.Append cmdX.CreateParameter("@RETURN_VALUE", 3, 4) cmdX.Parameters.Append cmdX.CreateParameter("@param1", 200, 1,50,cmdParam) cmdX.CommandType = 4 cmdX.CommandTimeout = 0 cmdX.Prepared = true set rsX = cmdX.Execute rsX_numRows = 0 I know for a fact that I have products in my dbase with the bit column 'OnSpecial' set to 1, yet no records are coming back. Any pointers would be most appreciated.
View Replies !
Dynamic Query In Stored Procedure
Hi, I have a table with values such as test1, test2, test3, test4, test5. I need to write a stored procedure with paramater (number TINYINT, number2 TINYINT), the number represents the field that I'm going to select and compare. For example if I pass in (1,5) I will need the fields test1 and test5 and store them in Temp and Temp2. How do I write the following to so it will dynamically select which field to use when passing the parameters? DECLARE @Temp TINYINT, DECLARE @Temp2 TINYINT, SELECT top 1 Temp = test1, Temp2 = test5 from table
View Replies !
Trying To Build Dynamic Stored Procedure
My existing ASP 1.0 site keeps getting hacked using SQL injections. I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search. I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck. Getting either errors or double LIKE statements. Need some help. string[] SqlKWSrch; SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ','); int AStop = SqlKWSrch.Length; int i = 0; foreach( string a in SqlKWSearch ) { if (i <= AStop) { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND "; } else { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' "; } i++; } 1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion. PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1 RETURN Thank you
View Replies !
Run Dynamic Query Using Stored Procedure
Hi, I need to create a stored procedure, which needs to accept the column name and table name as input parameter, and form the select query at the run time with the given column name and table name.. my procedure is, CREATE PROC spTest @myColumn varchar(100) , @myTable varchar(100) AS SELECT @myColumn FROM @myTable GO This one showing me the error, stating that myTable is not declared.. .............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters.. Plese help me?? Is it possible in stored procedure..
View Replies !
Dynamic Sql For Count In Stored Procedure
Hi all, I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project. If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql: Alter proc GetCountforemp @DestName varchar(200)=null, @total int output as begin SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee INNER JOIN Dest R on R.DestID=Employee.DestID WHERE R.DestName in ('''+@DestName+''') end
View Replies !
Dynamic Query In Stored Procedure
Hi i am trying to make the "userName" section of the code below dynamic as well, how can i do this, the reason being userName will not always be passed through to it. ALTER PROCEDURE [dbo].[stream_UserFind] ( @userName varchar(100), @subCategoryID INT, @regionID INT )ASdeclare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like ' + char(39) + '%' + @UserName + '%' + char(39) if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = ' + cast( @subCategoryID as varchar(10))if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = ' + cast( @regionID as varchar(10)) print @StaticStr exec(@StaticStr) )
View Replies !
Variables In Dynamic SQL In A Stored Procedure
I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance! Here's the sproc: ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT, @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri NVARCHAR(4000),@Sql_mr NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id INT, @StartRow INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum, dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video, dbo.tblCategorieen.CategorieFROM dbo.tblNieuws INNER JOIN dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId WHERE dbo.tblNieuws.NieuwsId <= @First_Id AND 1 = 1' IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId' SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr SELECT @Paramlist = '@xSearchQuery NVARCHAR(100), @xCategorieId INT'EXEC sp_executesql @Sql_sri, @Paramlist, @SearchQuery, @CategorieIdEXEC sp_executesql @Sql_mr, @Paramlist, @SearchQuery, @CategorieId
View Replies !
Permissions With Dynamic SQL Within Stored Procedure
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario... I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML. The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security. Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX. Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns. So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case? I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler. PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.
View Replies !
|
TRACKER
