Hi

You must do a left join and pull out all the records where there is no match. When you select form Master table and do a left join on StartDate...

Do you want to list the output of exec(@DSNo) as column data in the last select statement?

Select(@Filename),(exec(@DSNo)), ................

That wont make sens because exec(@DSNo) may return multiple results.

If you want @Filename...

How about creating an indexed view that has the calculated column.

Then create an index on that column and use the view in your query?

When the column is updated SQL should...

Yes, using dbo also works

It may be a better choice, so no new users needs to be created?

Thanks again

Great!! Thanks, now it definitely works

Sorry, I lied. sys.sqlmodules did not work. I tested with an admin login

When using a login who is only part of the public role it returns NULL

here is the query...

no code needed as sys.sql_modules did the trick 😛 . Thanks a lot.

Do EXECUTE AS only work for windows logins? I even tried DOMAIN\Administrator that has access to the database,...

Won't you get the desired result if you only run the query of the derived table?

Because you joining that back to Accounts it repeats for each account that starts with...

Have a look at how to use a Numbers table

You can extract the comma delimeted values and insert them into a table variable then use the table variable throughout...

You should compare it to 1 or 0

but the better way of doing it is to convert the 1 and 0 to a BIT type as they are INT values

so...

I see references to an IIF function

IIF(expression, truevalue, falsevalue)

where if the expression is true, the true value will be returned, else it returns the false value.

you can acheive the same...

Declare your parameter once before the first IF statement

I dont think IIF is a valid SQL keyword.

Here is a simple example of adding a 4 digit year to the end of a table:

CREATE TABLE testing(ID INT)

DECLARE @Q VARCHAR(100)

SELECT @Q = 'sp_rename ''testing'', ''testing_' + CONVERT(VARCHAR, DATEPART(YEAR,...

Have you tried doing it with dynamic SQL?

Thanks Andras, I did not know about the XML PATH way. It seems to be working a lot better.

I tested it on a small set of data to compare the...