I want to write a stored proc which will use a parameter, which will be the table name.
E.g:
@tablename << Parameter
SELECT * FROM @tablename
How is this possible?
I wrote this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAllInterviewQuestions]
@Alias varchar = null
AS
BEGIN
Exec('Select * FROM Table a...
I struggling with a problem I have in TSQL, I need to get the top 10 results for each user from a table that might contain more than 10 results.
My natural (and procedurally minded) approach is "for each user in table T select the top 10 results ordered by date".
Each time I try to formulate the question in my mind in a set based appro...
During an ordeal yesterday, I learned that you can't pass this query to EXEC():
@SQL = @SQL + 'WHERE ID = ' + @SomeID
EXCEC(@SQL)
Where @SomeID is an INT and @SQL is NVARCHAR. This will complain about not being able to convert NVARCHAR back to INT during execution.
I realized you have to do it like
@SQL = @SQL + 'WHERE ID = ' + CONV...
We're extracting booking information for our tennis courts from our SQL database into a simple results table to help us build a picture of court usage. It's pretty straight-forward except when it comes to bookings that last more than an hour.
At present, each booking results in a row in our results table. Each row contains a start-time,...
Hi there,
I have executed a code
SELECT CASE b.ON_LOAN
when 'Y' then
'In Lib'
when 'N' then
(SELECT c.duedate from book_copy a, book b, loan c
where b.isbn = 123456
and a.isbn = b.isbn
and a.book_no = c.book_no)
END AS Availability, a.isbn, a.class_number
FROM book_copy...
Hi, i'm having doubts on how to create a table for temporary storing error messages.
A lot of inserts and deletes
A GUID as foreign key, likely to be left outer joined
A simple nvarchar field for the ErrorMessage
Which primary key, if any, and indexes should I use for storing a lot of data for a short period of time?
Thanks
...
SQL Server 2008. I have this very large query which has a high cost associated with it. TOAD has Query Tuning functionality in it and the only change made was the following:
Before:
LEFT OUTER JOIN (SELECT RIN_EXT.rejected,
RIN_EXT.scar,
RIN.fcreceiver,
...
I have two tables. The first Holds a list of classes with basically an ClassID and a ClassName. The second Holds a list of classes that a pupil does attend using ClassID and PupilID (PupilID refers to a third table).
Is it possible to return for a given pupil a list of ALL Classes irrespective of attendance yet indicate the classes they...
I have a table:
ID A B C D
1 10 20 30 5
2 332 80 32 12
3 41 20 82 42
.
.
.
I want to query that gives me
A B C D
Where A contains the average of column A for the top 30 rows of the table, sorted by ID ascending, B contains the average of column B for the top 30 rows of the table, sorted by ID asce...
The following T-SQL code segment works but i'm wondering if there's a cleverer and/or less verbose way to accomplish the swapping of field values between two different rows. (The code has hard-coded pkey values for simplicity.)
BEGIN TRAN;
declare @swapFormSeqA int;
declare @swapFormSeqB int;
SELECT @swapFormSeqA = DisplaySeq
FROM Cus...
I am using ColdFusion for for a project and I have a written a query which I think can be faster using a Stored Proc, but I not a T-SQL person, so I am not sure how to do it to compare.
I am running an initial query which selects a number of fields from a table based on a dynamically built cfquery. I think I know how to convert this qu...
In SQL Server 2008 I need to update just the date part of a datetime field.
In my stored procedure I receive the new date in datetime format. From this parameter I have to extract the date (not interested in time) and update the existing values date part.
How can I do this?
...
Been investigating for a while now and keep hitting a brick wall. I am importing from xls files into temp tables via the OpenRowset command. Now I have a problem where I’m trying to import a certain column has a range values but the most common are the following. Columns structured as long numbers i.e. 15598 and the some columns as stri...
Hi,
I currently have the following stored procedure;
CREATE PROCEDURE web.insertNewCampaign
(
@tmp_Id BIGINT,
@tmp_Title VARCHAR(100),
@tmp_Content VARCHAR(8000),
@tmp_Pledge DECIMAL(7,2),
--@tmp_Recipients BIGINT,
@tmp_Date DATETIME,
@tmp_Private BIT,
@tmp_Template BIGINT,
@tmp_AddyBook BIGINT
)
AS
...
It is my day for weird errors.
I use a database project and as a part of Post Deployment, we use SQL Scripts to populate data in our tables.
alter authorization on database::atlas to sa;
go
ALTER DATABASE ATLAS SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
:r C:\Scripts\Script.DisableTriggers.sql
:r C:\Scripts\dbo\Script.dbo.PaymentMeth...
I have a query containing three inner join statements in the Where clause. The query takes roughly 2 minutes to execute. If I simply change the order of two of the inner joins, performance drops to 40 seconds.
How can doing nothing but changing the order of the inner joins have such a drastic impact of query performance? I would have th...
Hi all,
I have a table called "Sentence" that has the following fields:
ID <--- OK
NextID <--- FK To ID
Text
So if I had the following records:
*ID* *NextID* *Text*
1 12 The quick
3 40 jumps over
5 null lazy dog.
12 ...
We use 4-4-5 Accounting Periods. In case you aren't familiar with this, you can find info at Wikipedia.
To accomplish this, I created a date table with all the days for the next 10 years. I used the script (albeit modified) found here.
The creation script for my table is:
USE [RedFridayDates];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_...
I would like to get the out put of a stored proc call and create a permanent table with out specifying columns. Because the stored proc return lots of columns...
So.. how can I do this??
SELECT * INTO MYTABLE FROM MYSTOREDPROC @PARAM1 = 1, @PARAM2 = 'HELLO'
is it possible??
Example would help me alot
...
I have a table, let's call is [MYTABLE], with an FOR INSERT, UPDATE trigger.
The trigger needs to execute a stored procedure, which will do some work based on the changes made to [MYTABLE]. I can't move the stored procedure's code into the trigger.
So far, so good... since triggers execute after the changes are made, the stored procedu...