Tag Info

Hot answers tagged

10

A cursor should very rarely be your first approach. Especially if you've gone to the trouble of making a table type; the primary goal there is to avoid row-by-row processing, splitting strings, etc. Making some guesses about the destination table, its columns, and the name of the column in your TVP, you can insert multiple rows in the destination table by ...


5

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


3

It depends on the SQL SECURITY characteristic you defined when you created the procedure. SQL SECURITY for a stored procedure can be either DEFINER or INVOKER. If your user can not create table but the definer of the stored procedure can, then your user will be able to create a table through the stored procedure. By default, the SQL SECURITY characteristic ...


3

The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation. To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the ...


3

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan. If you are ...


2

Is it possible to execute stored procedure in another stored procedure in oracle Yes. Demo Let's say I have two procedures, P1, and P2, such that Procedure P1 takes an input as employee number, and gives the employee name as output. Procedure P2 will be called inside P1 which will take the output of procedure P1 as input, and give it as output. ...


1

you could store your result in a Table Variable DECLARE @TableVar TABLE (col1 varchar,col2 int,...) insert into @TableVar exec (Proc1) select Col1 from @TableVar


1

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server. Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is: bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T You would then attach the file ...


1

Yes you can send the report via HTML format, for example as listed in MS: Scenario: This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail ...


1

To my knowledge, there are no tools like doxygen that can make doc from parsing SQL annotated code. About keeping comments, note that comments in a stored procedure can be saved with the procedure code. When a CREATE PROCEDURE statement is sent from the mysql command line client to the server, make sure the mysql client is using the -comments option. See ...


1

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA. Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'yourdb' ...


1

If you are looking to set the ID variable parameter, you need to specify it as an OUTPUT parameter, and set it's value in your query: Create procedure addFaculty @ID int OUTPUT as begin IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty') begin Insert into CredentialsTable values ('iamafaculty'); Select ...


1

Credits to @sabin bio for this Using the Scope_Identity() returns the last identity in any table in the current session. The scope of the Scope_Identity() is limited only to the current scope which is where the last insert statement has been made. https://msdn.microsoft.com/en-us/library/ms190315.aspx


1

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons So there are good reasons to use prepared statements: Save on query parsing Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of using prepared statements: Query cache ...


1

I am reluctant to post this as an answer, because I don't have a tangible example to post with code, but I pointed Mark to Erland Sommarskog's great article, Giving Permissions through Stored Procedures, which has a section on Signing Procedures with Certificates. Cert signing seems to be the right approach for forcing data access through a procedure ...


1

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer. First i made one procedure to update one single row in the column: delimiter // create procedure update_amount_products (in id int) begin update categories set products_amount= (SELECT DISTINCT COUNT( products_to_categories.products_id ) FROM ...


1

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace ' ' || c.charges - c.payment || ' ' with ' ' || (c.charges - c.payment) || ' ' but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, ...


1

Create another Stored Procedure. In it, DECLARE a CURSOR for SELECT id FROM view WHERE another_id=100;. Create a loop; in the loop FETCH one id, then CALL deleteProcedure(id);. (See the manual and online examples for the details.)


1

To be clear, the issue is really with the PRINT command and not the SQLCMD utility. I am guessing that your variable is actually NVARCHAR(MAX), not VARCHAR(MAX) since the PRINT command is limited to only 4000 characters using NCHAR / NVARCHAR. Otherwise it can output up to 8000 characters using NVARCHAR / CHAR. To see that VARCHAR does go beyond 4000 ...



Only top voted, non community-wiki answers of a minimum length are eligible