Execute Big Dynamic SQL In Stored Procedure To Create View
Jul 20, 2005
I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure creates
a string of custom SQL statement and returns this string back to the
main stored procedure.
This SQL statements work fine on there own. The SQL returned from the
sub stored procedure are returned fine. The datatype of the variable
that this sql is stored in Varchar(I have tried using nvarchar also
same problem).
If I have more that 6 SQL statements concated then the main SQL gets
cut off. It doesnt matter in what sequence I create the main SQL.
-- Drop the previous View
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'custom_invoice')
DROP VIEW custom_invoice
Fetch Next From invoice_driver_cur
Into @L_driverid
-- Create the new View
Set @L_args = N'Create View custom_invoice As'
--Select @L_driverid
WHILE( @@FETCH_STATUS = 0)
BEGIN
Set @L_rowcount = @L_rowcount + 1
select @L_driverid
If @L_driverid = 2
Begin
Exec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @L_args + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 3
Begin
Exec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 4
Begin
Exec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 5
Begin
Exec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 6
Begin
Exec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 7
Begin
Exec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 8
Begin
Exec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
If @L_driverid = 10
Begin
Exec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,
@@sqlstmt Output
If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
Print @C
Fetch Next From invoice_driver_cur
Into @L_driverid
Continue
End
Close invoice_driver_cur
DeAllocate invoice_driver_cur
SET @sqlstmt = ' Select 1 SortOrder ,
( SELECT Drivers.Description) Description,
(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2)
)) / 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
Drivers.Driversid = 2 and
billingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''
and
fee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''
GO
/**********************************************/
This is what the Print Statement give:
/**********************************************/
Create View custom_invoice As Select 1 SortOrder ,
( SELECT Drivers.Description) Description,
(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2)
)) / 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
Drivers.Driversid = 2 and
billingreport.fromdate = '9/1/2004' and
fee.currentmonth = 'September ' Union Select 2,
(SELECT Drivers.Description),
(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))
/ 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
billingreport.fromdate = '9/1/2004' and
fee.currentmonth = 'September' and
Drivers.Driversid = 3 Union Select 3,
(Select Drivers.Description From Drivers Where DriversID = 4),
Count(*) * Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 4 )As decimal(6,2)) / 12,
(Select Drivers.Currency
From Drivers Where DriversID = 4)
From Fund Union Select 4,
(Select Drivers.Description From Drivers Where DriversID = 5),
(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +
BillingReport.Man_Reg_Transexch +
BillingReport.Man_Allo_Purch +
BillingReport.Man_Allo_Red +
BillingReport.Man_Allo_Transexch +
BillingReport.Man_Allo_Adj_Purch +
BillingReport.Man_Allo_Adj_Red +
BillingReport.Man_Allo_Adj_Transexch +
BillingReport.Man_Adj_Purch +
BillingReport.Man_Adj_Red +
BillingReport.Man_Adj_Transexch ) ) +
(Select Sum(Cast(satuscnt As int ))
From Awd_stub
Where CurrentMonth = 'September'))) *
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 5 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 5)
FROM BillingReport
Where billingreport.fromdate = '9/1/2004' Union Select 5,
(Select Drivers.Description From Drivers Where DriversID = 6),
( Sum( BillingReport.Auto_Reg_Purch +
BillingReport.Auto_Reg_Red +
BillingReport.Auto_Reg_TRansexch +
BillingReport.Auto_Allo_Purch+
BillingReport.Auto_Allo_Red +
BillingReport.Auto_Allo_Transexch+
BillingReport.Auto_Allo_Adj_Purch+
BillingReport.Auto_Allo_Adj_Red+
BillingReport.Auto_Allo_Adj_transexch+
BillingReport.Auto_Adj_Purch+
BillingReport.Auto_Adj_Red+
BillingReport.Auto_Adj_Transexch )+
(Select Sum(Cast(Processed_msg As Int))
From XML_messaging
Where CurrentMonth = 'September'))*
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 6 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 6)
FROM BillingReport
Where billingreport.fromdate = '9/1/2004' Union Select 6,
(Select Drivers.Description From Drivers Where DriversID = 7),
( ( a.Accountholder_Active_Accounts -
(select accountholder_active_accounts from billingreport where
Month(fromdate) = Month('9/1/2004')-1 ) )
+
( a.Accountholder_Zero_Balance -
(select accountholder_zero_balance from billingreport where
Month(fromdate) = Month('9/1/2004')-1 ))) *
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 7 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 7)
FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,
(Select Drivers.Description From Drivers Where DriversID = 8),
( Select telephone From cfxbill Where currentmonth = 'September') *
(Cast((select fe
I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck. Error: Unterminated String Constant. What is the problem? Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
In our development and test environments the developers need to create and execute stored procedures as dbo without having any other dbo permissions. If I place them in db_owner, they have too many permissions. Is there a way to address this situation?
I'm also curious how other companies address the subject of creating stored procedures in development and test environments. If I give developers create and execute permission in a database, all objects would be created as JohnDoe.storedprocedurename instead of dbo.storedprocedurename. Any help in this area is appreciated.
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table. I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user. If exists view 'custom_fields" drop view 'custom_fields' Create view custom_fields Select * From tblCustomFields And make this a view in the db named custom_fields. And I want to call it from a button click in my UI.
I am trying to create a view or Stored Procedure between different table
Table1 consist of the follwing Fields:
Ref_No: String hold the reference number, Unique Details: String
Table2: MasterRefNum : String, not Unique SubscriberRefNum : String, not Unique
What I am trying to do is that when the user enter a refernece number the system should return back 1- the details where Ref_No = the required refernece number 2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers
Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex create proc myProc as use [myDb1] go create view myV1 as select * from mytable go use [myDb2] go create view myV2 as select * from mytable go
go --- of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?
Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master] GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/ SET ANSI_NULLS ON GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details. Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT IF OBJECT_ID('#External_Referrals') IS NULL DROP TABLE #External_Referrals; GO DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated. Thank you, Dave
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object. Here are some clipits of what you need. Imports SQLDMOPrivate myServer As New SQLServer Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString() Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString() Dim storedProcedure As New StoredProcedure Dim qresults As SQLDMO.QueryResults Example Stored Procedure Try myServer.Connect(serverName, userName, password) storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) Label1.Text = "Creation of stored procedure successful"Catch ex As Exception Label1.Text = "Creation of stored procedure failed" Finally myServer.DisConnect()End Try Run Stored Procedure and Obtain Results myServer.Connect(serverName, userName, password) qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows MemberRowNo = qresults.GetColumnString(num, 1) Next myServer.DisConnect()
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??
How can I use in stored procedure the faction ‘’in’’ to select values, using execute and the values must not be specified before in the stored procedure
How do you execute a stored procedure in MS SQL Server?I can design and execute them from MS Access dev front end, but cannot seemto find how to run them in the Enterprise Manager.TIA.~ Duane Phillips.
I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!
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?
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.
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 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
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.
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*/
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)'
Hello, i need to create temporary table inside SP. i having one string variable @strQuery which contain dynamic query inside SP. i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table. so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..." when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl' If i removed Hash then it works fine. even for double Hash also its work fine. but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this? its very urgent for me. thanks in advance.
I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.
