Tagged Questions
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
0
votes
0answers
21 views
developing procedure in sql for global use
what type of complex procedure can i develop in SQL which will make others work easy as well ,
like in Java , i had developed a program for FTP so that anyone can import this program and can use it ...
3
votes
0answers
43 views
SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows
So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part:
ALTER PROCEDURE ...
0
votes
1answer
34 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment i have decided to create separate database just for stored routines storage. Mainly i am using them for reporting. ...
0
votes
2answers
61 views
How to Drop Tables using a variable in SQL Server?
DETAILS:
I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.
WHY?:
In my Access Database I am running a ...
1
vote
1answer
55 views
Custom sp_who/sp_whoUsers
I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
4
votes
1answer
141 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
6
votes
2answers
484 views
How to keep history of SQL Server stored procedure revisions
Note: I am not asking about full version control.
Is there any way automatically to keep a history of stored procedures on SQL Server.
Similar to how Google Docs automatically keeps a history of ...
11
votes
2answers
1k views
MySQL: Tree-Hierarchical query
SUB-TREE WITHIN A TREE in MySQL
In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
0
votes
2answers
375 views
Use of CASE statement in a MySQL query
I have 3 tables:
TASK (id_task (PRIMARY KEY), name)
PROJECT (id_project (PRIMARY KEY), name)
PROJECT_HAS_TASK (id_project, task_id)
PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK.
...
0
votes
1answer
288 views
MySQL - Create a function from another function
I want to create a function same like my old functions.
When I create a table, I can use `AS to other table as follows:
CREATE TABLE dummy2 AS SELECT * FROM dummy1;
Can I do the same thing with ...
2
votes
1answer
84 views
Stored procedures and network congestion
When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it ...
1
vote
1answer
655 views
Production SQL Server High CPU for a Single Query
I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008. I am not able to determine why the CPU is spiking for this single query.
I can trigger an ...
4
votes
2answers
2k views
How do I use the IF…ELSE condition in a WHERE clause?
I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.
Should this work?
CREATE OR REPLACE package body If_Else_Pack IS
PROCEDURE Moving
...
5
votes
2answers
375 views
PostgreSQL procedural languages - differences between PL/pgSQL and SQL
Can anybody please summarize the differences between:
http://www.postgresql.org/docs/9.1/static/xfunc-sql.html
and
http://www.postgresql.org/docs/9.1/static/plpgsql.html
?
Main points:
...
2
votes
2answers
2k views
Show procedure code in MySQL
I've tried to show the SQL code of a stored procedure called add_pixel:
mysql> SHOW PROCEDURE CODE add_pixel;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ...
9
votes
1answer
3k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
5
votes
3answers
670 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
4
votes
3answers
329 views
SQL Server stored procedure keeps restoring to previous one
There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure ...
4
votes
1answer
331 views
In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?
I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc.
For example:
CASE WHEN ...
5
votes
2answers
8k views
Granting Execute Permissions on Stored Procedures, Functions and Views
I am using a user which has read permission only. It basically has permission to
Connect
Execute
However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
0
votes
0answers
302 views
Grouping a comma separated value on common data [closed]
I have a table with col1 id int, col2 as varchar(comma seperated value) and column 3 for assigning group to them.
Table looks like
col1 col2 group
..............................
...
4
votes
1answer
2k views
Why does a query run slower in a Stored Procedure than in the Query window?
I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?
Here's what my query looks ...
3
votes
1answer
318 views
How to change database owner for login and database created inside stored procedure?
I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
0
votes
1answer
96 views
TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs
I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs.
Each office has already imported data ...
1
vote
1answer
697 views
Heterogeneous queries error using stored procedure
Stored procedure
ALTER PROCEDURE Proc_DayShift
AS
insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
...
2
votes
1answer
275 views
high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005
I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity)
SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ...
2
votes
4answers
6k views
Call a stored procedure from a trigger
I have created a stored procedure in mysql using the following syntax.
DROP PROCEDURE IF EXISTS `sp-set_comment_count`;
DELIMITER $$
CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
-- ...
3
votes
2answers
1k views
SQL Server 2008 T-SQL select hanging, but not dead locked
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
8
votes
2answers
755 views
How to delete a procedure which name is ambigous?
I am using informix...
I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a
DROP PROCEDURE myProc;
then i get a error message
...
2
votes
2answers
1k views
Primary Key on a View
While answering this question
How to add a Primary Key on a Oracle view?
A lot of questions popped in my mind.
1. Is it advisable to have primary key in a view.
2. Why anybody be intrested in ...
6
votes
4answers
2k views
Do SQL Server stored procedures cache data results?
I have heard this from friends, but I never investigated whether this is true.
Is it true that the data results of an executed query are stored in cache?
I mean, if I have a stored procedure like :
...
3
votes
3answers
2k views
Stored procedure executes too long
We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
2
votes
1answer
236 views
Virtual Column - Using it in view (Oracle)
I have made a virtual column in Oracle, using the following code.
CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as
lname varchar2 (256);
snumber varchar2 ...
1
vote
2answers
1k views
Print database_size from sp_spaceused
Using SQL Server 2008, I have found that the database size can be obtained using
exec sp_spaceused
This procedure returns two tables:
database_name | database_size | unallocated space
reserved | ...
3
votes
2answers
1k views
How to optimize this dynamic stored procedure
I have a stored procedure which is retrieving data using 20 tables.
Sample of the procedure:
CREATE PROCEDURE GetEnquiries
(
@EnquiryDate DATETIME = NULL
)
AS
...
4
votes
1answer
320 views
Kill CLR stored procedure process
I have a CLR stored procedure which exports a large table into a text file.
The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it ...
2
votes
2answers
6k views
How to include a datetime parameter within a stored procedure along with string query?
My stored procedure is as follows,
-- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime
--Select query
DECLARE @query nvarchar(max)
...
1
vote
4answers
759 views
How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?
I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query.
Simply i can use ...
2
votes
0answers
174 views
Stored procedures over SQL in the code, good idea? [duplicate]
Possible Duplicate:
What are the arguments against or for putting application logic in the database layer?
Assuming I got over the problem of maintainability and debugging.
My assumption is ...
8
votes
2answers
494 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
0
votes
0answers
111 views
how do i bulk copy a huge amount of data and keep the same table updated through stored procedure in SQL SERVER [duplicate]
Possible Duplicate:
How to move data from big table
In my project i need to fetch datas from different tables in a data base and create a new table with the same colmn names for all those ...
7
votes
6answers
705 views
How do you submit a List of items to a Stored Procedure to limit results? [duplicate]
Possible Duplicate:
Passing array parameters to a stored procedure
Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read:
WHERE ...
11
votes
3answers
632 views
Is table aliasing a bad practice?
I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:
SELECT t1.id, t2.stuff
FROM
someTable t1
...
5
votes
2answers
874 views
'Conversion failed' error with ORDER BY CASE expression
I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'.
I get the following error:
Conversion failed when converting the nvarchar value 'SK11270' to ...
2
votes
2answers
283 views
update stored proc ony fields that are passed to the proc and leaving others alone
i have a stored proc that does a simple update (see below).
i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want ...
3
votes
2answers
1k views
Sql Server output parameters
Is there a way to use named parameters when getting the output parameters of a stored proc? Currently my knowledge of output parameters is very limited. It looks like I have to use them in order of ...
3
votes
4answers
2k views
Examples of SQL transaction procedures for sales tracking or a financial database
I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions:
salesman creates new product ...
4
votes
2answers
415 views
Fetch first 9 records and make one more record which will be as “Others” in MySQL
I am fetching records from one table with count of one field with other field as name.
I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
2
votes
6answers
674 views
Optimize this SQL query for performance
I'm trying to optimize the following statement:
'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR)
+'/00000' +CAST(@number+1 AS VARCHAR)
The statement produces a value ...
