Tag Info

New answers tagged

0

As simple answer to the question, yes, it is possible to do this with a function. PostgreSQL has several very powerful function languages that you can use for this purpose. Being powerful, you can do some pretty neat things with them. You can also shoot yourself in the foot very easily. The solution is very brute-force and inelegant, and I wouldn't ...


5

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples: -- Fully random ordering SELECT * FROM dbo.Foo F INNER JOIN ...


5

Rather than saving your procedures in user tables, why don't you just set up a SSDT database project and use version control (TFS, Git, Mercurial, SVN or whatever you find appropriate) to take care of versioning? SSDT or other tools such as Red Gate's SQL Source Control will help you in keeping track of versions and deploying your changes to your ...


2

You can use sys.sql_modules (MSDN): select top 10 * from sys.sql_modules as m inner join sys.procedures as p on m.object_id = p.object_id where p.name ... It is better to alter them because you won't have to worry about existing permissions. If you drop and create the procedure, you have to set them back. DO: if not exists, create empty dummy SP and ...


1

Well, there's bad news, good news with a catch, and some really good news. The bad news T-SQL objects execute in the database where they reside. There are two (not very useful) exceptions: stored procedures with names prefixed with sp_ and that exist in the [master] database (not a great option: one DB at a time, adding something to [master], possibly ...


6

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where ...


1

Array is not supported in stored procedure, you can use temporary table. Try this code: CREATE PROCEDURE `getAttritionData`(IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT) DETERMINISTIC BEGIN DECLARE i INTEGER DEFAULT 0; DECLARE CNT_EMP INTEGER DEFAULT 0; DECLARE CNT_EMP_SUS INTEGER ...


0

I figured it out. The comparisons in the IF statements needed to be to a queried variable, e.g.: SELECT users.username INTO queried_username FROM users WHERE users.id=user_id LIMIT 1; Then you can compare using NOT LIKE like: IF (username NOT LIKE queried_username) THEN SET action_taken = CONCAT(action_taken, '[Username Updated]'); END IF; ...


0

Do you have DELIMITER statements around the CREATE...? Does VS have any way to debug Stored Routines? (I suspect not.) The failure to do it this way could cause trouble later: START TRANSACTION; SELECT ... from users ... FOR UPDATE; various SETs UPDATE ...; COMMIT; Another consideration... An "audit trail" may be better achieved via a TRIGGER wherein ...


0

You'll need to put a trigger on the siteArea table so that whenever it's updated or inserted, it'll call a stored procedure with an argument representing the new site area and that procedure will calculate the needed values and insert them in a table. Your question is too ambiguous for me to describe further.


1

You have to use dbname.schema.objectName e.g. dbname.dbo.MYTABLE in your tsql EXEC msdb.dbo.sp_send_dbmail @recipients='MYMAIL', @body='100 Daily documents downloaded', @subject ='100 Daily documents downloaded from user', @query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100''' EDIT: You only want ...


1

I notice you tagged the question netezza but I'm not sure that SQLCMD (suggested in the accepted answer) works with Netezza or just with SQL Server. An alternative is to use Aginity Workbench for Netezza which provides an unattended commandline option: "C:\Program Files (x86)\Aginity\Aginity Workbench for PureData System for ...


3

At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary to retrieve a valid result set will be the path chosen. SELECT * FROM t1 WHERE (col1 = 'foo') OR (1 = 1); The server will always return all of the rows, ...


0

So yes as you have mentioned about SQL injection the other advantage is what you guessed. Quoting from documentation: Using prepared statements with placeholders for parameter values has the following benefits: Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of ...


5

There are a few options that you could use for this. The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters: CREATE PROCEDURE [dbo].[sp_Example] @parameter1 char (8) = 'test', @parameter2 char (20) = 'test2'.... The second alternative to checking ...


0

I would recommend against this approach. While it may seem easier to do something like this it will, in the end, become a performance and maintenance nightmare for you. The time it will take you to write 7 procedures that do everything will be the same as if you just go ahead and create the 4 procs per table, believe me, I've tried it your way myself long ...


0

Maybe this query will help you to get CRUD operations in one procedure: Table 1: Customer create table customer ( id int not null, name nvarchar(50), phone nvarchar(20)) Table 2:CustomerAddress Create table CustomerAddress ( cusid int, city nvarchar(50), State nvarchar(50), Pin Nvarchar(10) ) Store Procedure: create Procedure uspCustomer ...


13

I would strongly advise against this route. While it seems advantageous to only have 7 stored procedures rather than 28, it will turn into a troubleshooting, tuning, and maintenance nightmare. I don't know what dbms you are using, but the advice should apply to all of them. From a troubleshooting and usage analysis standpoint you will have a hard time. ...


0

SUGGESTION #1 Check the user in the Stored Procedure and leave if it is not the right user DELIMITER // CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER COMMENT 'Accepts an ID and returns the record' ThisStoredProcedure:BEGIN SET @cur_user = CURRENT_USER(); ...


2

I'm coming in late, but I "do" security and *administration things. Is this the job, of a dba? to know who created what inside the sql? YES. It is important to have or create these logs wherever possible. However, it's also your responsibility, in my view, to not "open this box" until it becomes important. In other words--again, in my view--it is your ...


3

If you have default trace enabled and it has not rolled over, then you can use it to find who created the SP /* Object Altered Object Created Object Deleted */ SELECT TE.name , v.subclass_name , DB_NAME(t.DatabaseId) AS DBName , T.NTDomainName , t.NTUserName , t.HostName , ...


5

Yes there is a way The table to store the results of the trigger USE [SOME_DATABASE] GO CREATE TABLE [dbo].[ddl_objects_log]( [date] [datetime] NULL DEFAULT (getdate()), [login_name] [nvarchar](128) NULL, [nt_user_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [host_name] [nvarchar](128) NULL, [text] [xml] NULL ) ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns. My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.


0

The problem was my select query was returning more than one row, and my code was not handling them via a "loop" so it was not working and giving me errors. This is my new Procedure... BEGIN DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler. DECLARE dateTime datetime DEFAULT NOW(); DECLARE dateTimeCounter ...



Top 50 recent answers are included