ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,286 through 9,300 (of 10,143 total)

RE: Using a variable to form part of a table name in stored procedure

richard (12/4/2008)
That works - thanks Chris......so it was just doubling up the quotes basically?
Cool, well done.
There were a couple of changes to make between the original and the final,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 5:35 am

#907488
RE: Using a variable to form part of a table name in stored procedure

This works for me...just a couple of small changes
ALTER PROCEDURE dbo.Sun5_OFAC_Export
@bus_unit varchar(3)
AS
Declare @sql varchar(2000)
--build the SQL statement
SET @sql =
'SELECT '''...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 5:08 am

#907476
RE: Using a variable to form part of a table name in stored procedure

Richard, can you post the result of the print statement please?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 4:55 am

#907472
RE: Using a variable to form part of a table name in stored procedure

Hi Richard
The statement is longer than NVARCHAR(1000), so increase the value:
DECLARE @sql NVARCHAR(2000) should be more than enough.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 4:44 am

#907468
RE: exist function

bhushanhegde (12/3/2008)
Hi,
I want check a perticular table is blank or having any rows? is it possible with using exist keyword? itried with isnull. but it is not working.
select isnull(empid,0) from...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 4:34 am

#907463
RE: Using a variable to form part of a table name in stored procedure

Hi Richard
Give this a try. When you've got it working, then's the time to put it into a stored procedure. Notice the PRINT statement? Take the output from this, paste...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 3:45 am

#907453
RE: Using SOUNDEX, How to match whole text.

Hi Ankur
Please have a quick read of the link at the bottom of my post. We need a larger data set as test data, posted as a script to create...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 2:09 am

#907414
RE: Proc is giving poor performance

Skull Killer (12/3/2008)
Hi there
U can also create a var @ActionTable TABLE(id INT IDENTITY(1,1),Action VARCHAR(20))
insert the possible actions into this table
INSERT INTO @ActionTable (id,Action)
SELECT 'NEW'
UNION ALL
SELECT 'DELETE'
UNION ALL
...
or better...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 4, 2008 at 12:41 am

#907390
RE: Cursor Help

If you have to do it this way, then something like this should suffice:
[font="Courier New"]-- Make some sample data
DROP TABLE #TablefieldPositionLength
CREATE TABLE #TablefieldPositionLength ([field name] VARCHAR(30), startingposition INT, fieldlength INT)
INSERT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 9:49 am

#907002
RE: Stored procedure loop problem

Hi Adam
If you run this code snippet, it will return dupe pairs. How would you identify which of each dupe pair to delete?
SELECT *
FROM vFRDonation a
INNER JOIN (SELECT OriginalTransNumber
FROM vFRDonation
WHERE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 8:56 am

#906946
RE: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Top work, Jeff. You have a rare talent for mixing informal narrative with a technically challenging subject - and with ruthless precision 😎
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 8:41 am

#906932
RE: SQL 2005 Data Type Error

GilaMonster (12/3/2008)
Correct me if I'm wrong, but hasn't this been asked and answered twice before? (exact same insert statement)
http://www.sqlservercentral.com/Forums/FindPost610162.aspx
http://www.sqlservercentral.com/Forums/Topic610173-1291-1.aspx
Yep, thanks for the confirmation, Gail. I'm outta here.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 5:11 am

#906768
RE: SQL 2005 Data Type Error

Heh - no worries. Can you please run the following two statements and post the values they return?
SELECT MAX(LEN(RTRIM(i.Client))) AS Client_LEN,
MAX(LEN(RTRIM(i.CountryCode))) AS CountryCode_LEN,
MAX(LEN(RTRIM(i.ClientID))) AS ClientID_LEN
FROM dbo.vwImportAllNewClientIDs i
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 5:02 am

#906756
RE: SQL 2005 Data Type Error

Hello
I've taken the liberty of simplifying/reformatting your code to make it a little more readable:[font="Courier New"]INSERT INTO dbo.tblDeals(Client,Bought_Sold,RIC,
   Trade_Date, Price, Counterparty_Code,
   Dealer, Commission, Consideration,
   Quantity, Currency, SEDOL, Participant_ID,
   FIDESSA_Ref, FX_Rate,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 4:44 am

#906740
RE: Select statement

eseosaoregie (12/3/2008)
I currently have a select where for one of the fields I need to change the output if it is null or contains the code 'v918899' For eveything else...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 3, 2008 at 4:06 am

#906723

Viewing 15 posts - 9,286 through 9,300 (of 10,143 total)