We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.
From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.
This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.
So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?
Or if not, is there some round-about yet effective way of doing this?
This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.
I'm writing a stored procedure, in which I dynamically create an SQL statement. At the end of this, the SQL statement reads like:
Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.
If I hard code a statement, like
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger that works fine (although it brings back a count of all the lines).
But if I modify the dynamically created SQL Statement from earlier on to:
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0 it doesnt work - it complains: Must declare the scalar variable "@LineCount".
Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)
So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).
I need to know how to:
- Get a list of all temp tables created - Get a list from each temp table of the columns created.
It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:
reportMonth - Just a non-queried list of months for the user to select reportYear - Generated using the following query from dataset "Years":
declare @curYear int set @curYear = 2000 declare @yearTable table (repYear int not null) while @curYear <= year(getdate()) begin insert into @yearTable(repYear) values (@curYear) set @curYear = @curYear + 1 end select * from @yearTable
This generated a list like:
2000 2001 2002 etc.
I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.
CREATE PROCEDURE PROC1 AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10) ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (23,12,10) ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1' else '0' end) + MAX(case when A.intpreferenceid = 12 then '1' else '0' end) + MAX(case when A.intpreferenceid = 10 then '1' else '0' end) AS PREFER FROM CUSTOMER GROUP BY A.INTCUSTOMERID ORDER BY A.INTCUSTOMERID END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose, I have four data tables with the same structure but records may be different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I want to that two records by filtering all tables and sum of rates and want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
The current way I have my asp.net 2.0 web app running reports is, based on an interface the user selects the criteria for a report. The .RDL file is created dynamically based on the user's selections. I then need to SOAP the dynamically created report to the report server and then the report runs fine. BUT it requires Adminstrative rights to do this. Can this be accomplished without giving the local machine admin rights. I am sorry if this question has been answered before but i have not been up here in a while.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
First off, this is a cross post which is also located here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND GO
CREATE FUNCTION RECTANGULAR_XSECTION (@fWidth float, @fHeight float)
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
( --define the parameters that are needed @userID char(32), @userName varchar(50), @status char(10), @type char(10), @password varchar(50), @firstName varchar(100), @lastName varchar(100), @email varchar(200), @domain varchar(50), @pwdExpiry int, @badLogin int, @lastLogin datetime, @full varchar(8000), @read varchar(8000), @noaccess varchar(8000) ) AS BEGIN TRAN tmp1 --First, insert the user into the system INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate) values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user declare @arrayValue char(32) declare @rightID char(32) declare @sepPos int while patindex('%,%',@full)<>0 BEGIN select @sepPos = patindex('%,%' , @full) select @arrayValue = left(@full, @sepPos - 1) -- replace the value with an empty string select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id declare @strID char(32),@tmpStr varchar(40) set @tmpStr = newid(); set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write') if(@rightID IS NOT NULL) --insert the records that are full access INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID) VALUES(@strID, @arrayValue, @rightID, @userID) else RAISERROR(50100,15,1) END COMMIT TRAN tmp1 IF @@TRANCOUNT > 0 RAISERROR(50101,15,1) ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo ( @VendorNum nvarchar(24) ) AS
SELECT P.ProductID AS ProductID, P.ProductShortName AS ProductName, P.ProductDesc AS ProductDesc, U.UnitDesc AS Unit, P.VendorProductNumber AS VendorNumber, V.VendorName AS VendorName, P.Price AS Price, P.ImageThumb AS ImageThumb
FROM tblProducts AS P INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE V.VendorsVendorNo = @VendorNum AND P.Inactive = 0
ORDER BY ProductName, VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name from store (server A) where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine. Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime exec serverB.dbo.sp_max_date_from_store @maxdate select store_name from store --(server A) where date_created >= @store
Any help or suggestions would be greatly appreciated! trevorb
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors) Management is looking at stored procs to improve performance, but I am wondering if the db is in this state will there really be a gain. I am pushing for normalization first, but if anybody has any ideas or opinions I would appreciate
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.
Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?
First post on here, so please excuse any ignorant parts of my question :)
We've recently had to migrate an application from one server to another, and had lots of problems getting it to work (NT4/SS7 -> 2K3/SS2K5). After much digging around I found the info on SQL server 2k5 changing the SUSER_NAME to SUSER_SNAME (I think from reading the change was actually from SQL Server 2000 - that correct ?).
Anyway I updated some of our stored procedures to correct that issue, and things *seem* to be working again (<-- famous last words, I know :eek: ), but I've been trying to find details of any other changes, particularly command changes, that I may need to make to our stored procedures.
To be honest I've found the info on Microsoft's main site and technet site next to useless - all seems to discuss very generic high-level procedures for database migration etc.. I didn't even find the SUSER change on there.
Could anyone point me to some useful information on other changes to look out for (within the SP's) ??
Thanks in advance for your help, and for taking the time to read this :beer:
I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.
First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.
So how would someone prohibit viewing of a stored proc like this?
I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records. To do testing we run that stored proc manually, also. Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.
What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently. (May be semapohres,mutex or something like that?)
(I am trying not to use a table to store whether the stored proc is running or not)
I wrote a stored procedure and set it as a schedule. it has no problem when I execute this SP in query analyzer. But it will show error when I execute it in schedule job. I think the main reason is I have created a DDL trigger that will fire when DDL_DATABASE_LEVEL_EVENTS happens. Any solution?
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Can someone please point me in the right direction (guidance or url to a utility or article) as to how we can write a script to scan all of the stored procedures in our database for any reference to a field name were are about to change? Also, can we use such a script to do the actual change from the old field name to the new one?
Hello,It might be a basic question, but there it goes:I have a store procedure that first checks if the record exists and, ifit doesn't, inserts the new record.Is the scenario below possible?(thread1) USER1 -> check if record "abc" exists(thread2) USER2 -> check if record "abc" exists(thread1) USER1 -> "abc" doesn't exist(thread2) USER2 -> "abc" doesn't exist(thread1) USER1 -> add "abc" as new record(thread2) USER2 -> add "abc as new record (OPS, this is an error,because "abc" already exists, it was just inserted by USER1)I am wondering if this kind of concurrent, multi-threaded accesshappens with stored procedures.If yes, can I execute a procedure from start to finish without anyother simultaneous procedure interrupting?I appreciate any information about this.Leonardo.
I have some users that I need to run stored procedures, but they can'tseem to run them unless they are in the db_owner role of the database.How do I give them access to run the stored procs without giving themthe complete rights of the db_owner role?Thanks in advance.
I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs?
The thing is when I am running the Stored procs in 2005, its performing really slow in first run.
I have read in many articles that Dynamic SQL should not be used in Stored Procedures as it is not a Best Practice. So Can anyone tell me how to handle the following scenario without using Dynamic SQL in Procs. The scenario is as follows
I have to write a Procedure which will generate a Report from a Table. For this purpose I have to Select about 20 columns from a Table (lets say Table A). Now the report has 5 filters which are completely optional. I mean the user might fill few of the filters or all the filters or none of the Filters
So can anyone help me out in this scenario because my "where" clause is completely Dynamic and I am unable to understand how to handle my where clause without writing Dynamic Queries. Also I am trying to write this query on SQL Server 2000.
If anyone can give me some examples or articles which can be of help it would be great. Please help
