OK.. let me simplify my query:

select * from vendorInfo where activetodate >= Convert(datetime, GetDate(), 101)

I would like to get all the records where activetodate is current date or future date...

But...

When I change

Convert(datetime, GetDate(), 101) to

Convert(date, GetDate(), 101)

I see following error:

'Type date is not a defined system type.'

Sorry I am actually using SQL server 2005...

But the query you wrote is not working because the output sql looks something like:

select count (*) from table where clientName = @clientName....

which is actually supposed to be

select count (*)...

Thanks for the quick response and the solution, had to modify it slightly as below:

CREATE PROCEDURE [dbo].[pr_GetVendorInfo]

(

@ClientName nVarChar(15),

@VendorName nVarChar(40),

@InvoiceType nVarChar(30)

)

AS

declare @sql nvarchar(2000)

set @sql = 'SELECT COUNT(*)...

Sure.. thanks for the inputs.. what would the dynamic sql syntax look like??

Appreciate any help with the syntax to accomplish this.. thanks!

Thankyou Thankyou Thankyou @sean Lange!!!

Worked like charm!

Sturner.. Thanks for the helpful suggestion!

OK, here is my stored procedure

CREATE PROCEDURE [dbo].[pr_insertAgencyVendorInfo]

( @ClientName nvarchar(15),

@VendorName nvarchar(40),

@TaxID nchar(9),

@ActiveFrom datetime,

@ActiveTo datetime)

AS

insert into AgencySupplierPartner

(ClientName,VendorName,TaxID,ActiveFrom,ActiveTo)

VALUES

(@ClientName,@VendorName,@TaxID,@ActiveFrom,@ActiveTo)

------I want to modify so that I get back errorMessage as output...

thanks.. but it's not very helpful.

If someone can provide an exact sample, would appreciate it!

Thanks for both the responses.

I will go with the Lowell's solution as it looks a bit simpler.

I am not passing the clientname because it could be anything that comes first when ordered in ascending order.. today it could be A123 and tomorrow if I add a...

Thanks for the suggestions!

Thanks for asking for that question!

Discovered the problem and the solution.

Yes! there is a difference. when a new column is added at the end, it just alters the table by...

nvarchar(400) we are trying to add it in the middle of the existing columns?

does trying to add it at the end of the columns matter?

it's a big table with 1,415,718 rows.

The initial size of the database was 34.373GB

When I tried to add a new column, the size increased to 36.549GB

Auto growth setting is set to 200MB unrestricted growth