Dynamic Cursor In Stored Procedure

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 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 !

---

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 !

---

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 !

---

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 !

---

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?

procedure XX
DECLARE cc CURSOR FOR SELECT....
OPEN cc
...
WHILE @@FETCH_STATUS = 0
BEGIN

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 !

---

Problem In A Dynamic Stored Procedure---help
I create a stored procedure as following:
........

CREATE Procedure myAutoSearch
(
@Make varchar(50),
@Model varchar(50),
@AutoType varchar(50),
@Miles float,
@Zipcode varchar(5)
)
AS

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'

if @RowCount > 0
BEGIN

SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make

END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...

....................

This stored procedure work very well.

The question is how I add some dynamic condition inside the where condition.

I want to add:

If @Model <> "See All Models"
AND a.Model = @Model'


If @AutoType <> "New/Used"
AND a.Condition = @AutoType

I try several ways, but fail.

If you know how to add these two dynamic parameters into the condition of stored procedure, please help.

View Replies !

---

Building A Dynamic Stored Procedure
Hi

I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success

For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)

My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),

@smoker tinyint),
@keyword
varchar(250))

AS

DECLARE @SQL Varchar
(4000)

SELECT  @SQL =   'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM         dbo.user_accounts INNER
JOIN
                     
dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE     (dbo.user_accounts.profileComplete =
1)'

IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'

IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'

IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'


IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'

IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'


EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need

Am I Missing something glaringly obvious?
Any help or advice gratefully received

Thanks

View Replies !

---

Dynamic Stored Procedure Errors
Hi

I am getting the following error

Syntax error converting the varchar value 'Select * from Residential WHERE Price BETWEEN ' to a column of data type int.

when running the following SP.



CREATE PROCEDURE testing
(
@Locationnvarchar(100)=NULL,
@TypeHomenvarchar(50)=NULL,
@MinPriceint=0,
@MaxPriceint=9999999999,
@Bedroomsnvarchar(2)=NULL,
@BathsSearchnvarchar(2)=NULL
)
AS

Declare @strSql char(255)
Set @strSql="Select * from Residential WHERE "

Set @strSql=@strSql + "Price BETWEEN " + @MinPrice + " AND " + @MaxPrice
If @Location is NOT NULL
Set @strSql=@strSql + ' AND city = ' + @Location

If @TypeHome is NOT NULL
Set @strSql=@strSql + ' AND Type = ' + @TypeHome

Set @strSql=@strSql + ' AND BDRM >= ' + @Bedrooms
Set @strSql=@strSql + ' AND BATHS <= ' + @BathsSearch
Set @strSql=@strSql + ' AND IDX = Y'

Exec(@strSql)



What is causing this error?

Thanks in advance

View Replies !

---

Dynamic Where Clause In Stored Procedure
Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @Path, @Status, @Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.

View Replies !

---

Dynamic ORDER BY Within Stored Procedure
I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.


CREATE PROCEDURE procedure_name
@regNum varchar(14),
@sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @regNum
ORDER BY Obs_Date DESC
GO


Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:


ORDER BY CASE WHEN @sortOrder = 1 THEN Obs_Date END DESC


but I frequently get the following error when I try to do so:

"ORDER BY items must appear in the select list if the statements contain a UNION operator"

If anyone can offer any suggestions, I would appreciate it. Thanks.

View Replies !

---

Dynamic WHERE Clause To Stored Procedure
Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:

---------------------------------------------------
@crit varchar(100)

SELECT fldID, fldName FROM tblUsers
WHERE @crit
----------------------------------------------------

Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.

cheers!
pelle

View Replies !

---

Dynamic Use Of Stored Procedure Resultset
Hello,

I have a stored procedur like this:

--------------------------------------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @SQLString )

select * from #T1
--------------------------------------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werner

View Replies !

---

Dynamic SQL Statements In A Stored Procedure
Hi

I have a small problem writing a stored procedure in a SQL Server 2000 database.

I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server don´t generate a resulting SQL-statement.

Example:

CREATE PROCEDURE spDynStatement
AS

DECLARE @sPartOfSQLStatement NVARCHAR(100)

-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .

-- As substitute I insert the string expression
SET @sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''

-- SELECT @sPartOfSQLStatement results in: '1' , '1.5'

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @sPartOfSQLStatement ) -- does not work

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO

Thankfull for ideas on how to solve my problem,

Peter

View Replies !

---

Help With Dynamic Where Clause In Stored Procedure
I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run.

at this point i keep getting errors in the section bolded below
it never seems to recognize anything after the if @myColours <> 'SelectAll'

CREATE Procedure PG_getAdvWheelSearchResults3
(
@SearchDiameter NVarchar( 20 ),
@SearchWidth NVarchar( 20 ),
@minOffset int ,
@maxOffset int ,
@boltpattern1 NVarchar( 20 ),
@VendorName NVarchar( 40 ),
@myColours NVarchar( 40 )
)
As
BEGIN TRANSACTION
SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image,
dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2
FROM WHEEL_CHARACTERISTICS INNER JOIN
dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN
dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN
FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN
dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN
CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND
WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN
dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID
WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y')
AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039')
AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter)
AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth)
AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1)

if @myColours <> 'SelectAll'
and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours
end if


AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset)

ORDER BY CLIENT_WHEEL_PRICES5.Price asc
COMMIT TRANSACTION
GO

Anyone know how i should word the if...statements?
I have not found anything that works yet.
Thanks

View Replies !

---

Why Is This Stored Procedure Slower Than Dynamic SQL?
 

We have a procedure that does some complicated table joins to populate a temporary table (declared in the proc) and then extraneous rows are removed from the table and the remaining rows are returned.
 
On the production server the procedure takes 22 seconds, but the identical query in a dynamic session window of SQL Server Management Studio takes 2 seconds.  That doesn't make any sense.
 
We've profiled the procedure and discovered that the small loop at the end of the query runs through twice with each iteration taking 10 seconds.  Again - the same section (but dynamic) profiles in at 0 seconds.  We further narrowed it down to the compound delete statement from the @tblResults table.
 
Here's the looped section of code:
 

-- now delete the entries with no content

declare @deleted int

set @deleted = 1

declare @tmp table ([Id] int)

while @deleted <> 0

begin


delete from @tmp

-- get all entries with no child nodes

insert into @tmp ([Id])


select distinct r.[id] from @tblResults r

where r.[id] not in

(

select [Id] from @tblResults

where [Id] in (select distinct ParentId from @tblResults)

)
 

-- delete entries with no documents and region match
-- (this is where the slowdown occurs)

delete from @tblResults

where [id] in

(


select t.[id] from @tmp t where t.[id] not in


(select distinct dm.metatagid from documentmetatag dm

where dm.documentid in


(select distinct dr.documentid from documentregion dr, @region r

where (dr.regionid = r.regionId or @regionIds is null)

)

)

)
 

select @deleted = @@rowcount

end

 

 
We then return all rows from the @tblResults table.
 
We are totally stumped on why the procedure would take so much longer than running the same query in a SQL Manager window.  Is there some issue with temporary tables as variables in a procedure?  Any ideas anyone?
 
Thanks,
John
 

View Replies !

---

How To Construct Dynamic Sql In Stored Procedure
Hi Guys.
 
I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this
 
 

CREATE PROCEDURE P_SAMPLE

(

@P_ONE NVARCHAR(240)

)

AS

BEGIN

DECLARE

@TMP_CNT INT

BEGIN

EXEC ('SELECT @TMP_CNT = 1')

-- PRINT @TMP_CNT

END

END
 
 
 
Regards
Mani
 
 
 

View Replies !

---

Dynamic ORDER BY In Stored Procedure
Hello,

I have this stored procedure:
SELECT * from purchase
ORDER BY
CASE @OrderBy
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as bit)
END

the @OrderBy is a nvarchar(100) parameter
traveller_name is an nvarchar(100) field
canceled is a bit field


When I execute the stored procedure it works fine until I execute it sorting by canceled field. After that, I cannot sort it again using the traveller_name field. I get this eror:
Conversion failed when converting the nvarchar value 'Jason' to data type bit.
(Jason is a record in the traveller_name)
Removing the castings and sorting by just the column name does not help.

Any ideas?
Thank you

View Replies !

---

Highly Dynamic Where Clause In A Stored Procedure
I have a situation where I'll need to get results from tables based on totally arbitrary filters. The user can select the field to compare against, the value, the comparison operator, and the boolean operator, so each bit in brackets would be configurable:[field] [>] [value] [and]The user can specify an arbitrary number of these, including zero of them. I like the coalesce function for situations that are a little more structured, but I think I'm stuck generating a dynamic query for this -- please correct e if I'm wrong! 

View Replies !

---