Search results for query: *

Is this the kind of thing you're looking for? It fills in the gaps from the example data: DECLARE @tabA TABLE (gname varchar(10)) DECLARE @tabB TABLE (rid char(2), gname varchar(10)) INSERT INTO @tabA SELECT 'Alpha' AS gname UNION SELECT 'Beta' UNION SELECT 'Gamma' UNION SELECT 'Delta' INSERT...

First time I've heard of synonymns in SQL Server. Really useful. Thanks George!

Here is a neat solution to rounding GETDATE(): http://stackoverflow.com/questions/249794/how-to-round-a-time-in-t-sql Extrapolating from that, I got these: SELECT CONVERT(datetime, ROUND(CAST(GETDATE() AS float) * 24,0) / 24) AS RoundToHour SELECT CONVERT(datetime, ROUND(CAST(GETDATE() AS...

The 'physical' order of the columns in a table won't make any difference to performance. The only instance I can think of where this could cause a problem is if you have an existing query like this SELECT * FROM tbl1 ORDER BY 1, 2 which references the columns by location, but this is really...

I've been looking for a solution for a problem like this for months. As George has said, the only way to get a true answer is to buy a full blown SQL parser. The closest I got was to dissect the execution plan and pull out any column references it contained. These references contain enough...

Assuming the format of the imported values is consistent, the following example will convert them: DECLARE @impval varchar(100) SET @impval = '6.745,05 €' SELECT CAST(REPLACE(LEFT(@impval, LEN(@impval) - 2), '.', ',') AS money) / 100 This: 1. Chops off the ' €' from the end of the string...

You would have to use a dynamic query to do this. eg: DECLARE @SQL nvarchar(MAX) SET @SQL = 'SELECT Col1, Col2' /* include Col3 only on the 15th of each month. */ SET @SQL = @SQL +(CASE WHEN DATEPART(d, GETDATE()) = 15 THEN ', Col3' ELSE '' END) SET @SQL = @SQL + ' FROM Tab1' EXEC...

Hi Kat. Temporary tables are unique to each session. If two people create temp tables with the same name in two different sessions, they would each be using separate tables. It is possible to create a global temp table by prefixing the table name with ##. A global temp table would work the way...

There are a few ways you can do this. But, an Apply is the most useful since it can also be used to pick up other fields, such as the name or value field, from the previous row. Here's an example: DECLARE @tbl1 TABLE (id int, name varchar(50), value int) INSERT INTO @tbl1 SELECT 1, 'Anne'...

Assuming the Password column contains the key that was used to encrypt the card number, you should be able to use the stored procedures laid out here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258

If NULL is a legitimate value, you'll have to use some unique default values to identify non-filled parameters. The LastUpdated calculation would have to check whether any of the parameters have changed from their default values. Here's an example (not tested): ALTER PROCEDURE...

PWise. I had high hopes for your suggestion but, annoyingly, the dbid value gets updated when the session executes the sproc. I've had a hunt through some of the other sys tables, but none of them seem to hold the info I need. It looks like I'll have to set up the sproc with an extra @dbname...

DECLARE @datetable TABLE (startdate datetime , durationmins int) INSERT INTO @datetable SELECT '1900-01-01 09:00:00', 210 UNION ALL SELECT '1900-01-01 09:00:00', 1440 SELECT startdate , durationmins , DATEADD(minute, durationmins, startdate) AS enddate FROM @datetable

I'm trying to write a stored procedure which can identify which database it has been called from. Using the following example: USE database1 GO CREATE PROC dbo.uspPrintDbName AS PRINT DB_NAME() if I call it from a different database: USE database2 GO EXEC database1.dbo.uspPrintDbName it...

Check the Retries field in the properties of that job step. It may be that the sproc returns an error the first time it's run. Does it run normally if you execute it manually?

Sounds like you will have to build a dynamic pivot query in a stored procedure. It won't be possible from a view. There is a good example of a dynamic pivot here: http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx

Hmm.. Base 36? That's a new one by me. The following function should provide what you need. I've transcoded (is that a word?) it from the orafaq page and switched the '/16' bits with '/ 36': CREATE FUNCTION dbo.ufnDecToBase36 (@val int) RETURNS varchar(MAX) AS BEGIN DECLARE @hexval...

I'd have said to do it the other way around and remove the 'Base' part. update B

I don't use Oracle, but according to http://www.orafaq.com/wiki/Hexadecimal , dec2hex returns a hex value, not ASCII. Are you using a custom function? I can't see how any of the values in your conversion table relate to the value 5044857.

I think I understand what you are after. You want to pull the all of the sales records but only show the minimum view value alongside each record. The PARTITION BY clause let's you do this. eg. DECLARE @test TABLE(ID int, sales int, views int) INSERT INTO @test VALUES (1, 1, 10) INSERT INTO...