New answers tagged

-1

If this is Mysql, instead of "+" use concat().So the line will be SET @Where = concat(@Where , 'CLASS LIKE \%' , @SearchText);


-2

According to me you can write case for name. you can also use and operator with age in if clause, but that would be longer process if names are more.


0

It sounds like what you are looking for is a "unique" constraint: ALTER TABLE Table1 ADD UNIQUE(Series, Orders) That will cause an error to be raised when you try create a row (by inserting new or updating an existing one) that duplicates an existing combination of Series and Order. Depending on the rest of the database design, these columns might be a ...


2

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL. A few points to get you started on the performance of your query: Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain. tblMetaData ...


8

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically. You don't have an explicit return value in the event that ...


4

You don't have a BEGIN/END for the IF statement so it is executing the RAISEERROR regardless of the ERROR_NUMBER. I've added the block around the SELECT and the RAISEERROR and added a RETURN (0); in an ELSE block. ALTER PROCEDURE [dbo].[pSetUserItemLike] @UserID int ,@ItemID int AS BEGIN SET NOCOUNT ON; -- for exception handling DECLARE ...


1

Beware when using the same name: WHERE `language_code` = language_code Suggest you precede any arguments or locally DECLAREd variables with a _. You can probably combine both procedures into a single SQL statement.


-1

My company has just developed this new tool (free) that helps you to easily extract scripts for SQL databases, can do comparison, can launch WinMerge for quickly comparing scripts to live database, and can also synch differences both updating the scripts or applying the changes to the database (except for tables, which would involve more complexity and more ...


3

I use this script that I developed to help me find linked server references, but can be used to find any text string in multiple objects. /********************************* Name: Text Searcher Author: Jonathan Fite Created: 1/16/2015 Purpose: To enable one to more easily search for objects by an indicated string. Usage: - ...


4

Well, you could parse the procedures (as long as they aren't encrypted) and jobs on server A looking for the name of the linked server - but keep in mind these requests could be passed in through an app, coming in from ad hoc SQL, built dynamically, etc. so this won't catch everything. This can also produce false positives if the linked server name is a ...


1

This is probably best to insert all items at once or at least avoid have to split string on the SQL Server side. You could convert json to xml similar to this: <Order OrderId="" OrderDate="" CustomerId="" Price="300"> <Items> <Item ItemId="103" Qty="2" Price="100" /> <Item ItemId="123" Qty="2" Price="100" /> ...


2

Depending on what you are trying to achieve with this, you may be out of luck. First of all, names cannot be parametrised in MySQL otherwise than using a prepared statement. – So, can we use a prepared statement here? No. Prepared statements are not allowed in triggers, according to the manual: SQL syntax for prepared statements can be used within ...


2

Your problem is due to the same variable name is used for the parameter and local variable. i.e, wynik used as INT in the local variable as well as DOUBLE in the parameter of the nowyRanking function. In this case changing the parameter name will solve the issue. In the below code I renamed the parameter name wynik DOUBLE to wynikDouble DOUBLE and handled ...


0

Creating such a stored procedure is probably going to be complex and error prone. That's why there're are temporal databases or temporal features in a database. If you really wanted to do this with a sproc, you can have a duplicate schema with a version number column added to all tables. That way, on each write operation, you can first call the SP which does ...


0

You dynamically build the query. Use CONCAT to construct the SQL to include the column name provided; PREPARE it`; EXECUTE it; DEALLOCATE


1

SELECT populateIfNotAlreadyIn(1, 'val1'), populateIfNotAlreadyIn(2, 'val2'), populateIfNotAlreadyIn(3, 'val3'), populateIfNotAlreadyIn(4, 'val4'), populateIfNotAlreadyIn(5, 'val5') ; Meanwhile, you could shorten the function to just RETURN EXISTS(SELECT * FROM t WHERE id = idArg);


0

While I don't know of a solution that solves your specific problem, you can search the raw source code of your stored procedures using the sys.sql_modules view. This, however, won't work with encrypted (i.e. WITH ENCRYPTION) procedures. SELECT s.[name]+'.'+o.[name] FROM sys.procedures AS o -- or sys.objects for all objects INNER JOIN sys.schemas AS s ON ...


1

This cannot be accomplished. Why ? According to Can MySql stored proc call a system cmd? (from July 19, 2009 11:47AM) This question is asked several times a week. The answers are (i) not out of the box, for very good reasons, (ii) you could write and link into your server a UDF which calls the operating system, but that would create a glaring security ...


4

This is the syntax to drop it if it exists DROP PROCEDURE IF EXISTS db.sp_tmp_90days;



Top 50 recent answers are included