Hi, If you just what to store the COUNT value in a variable (@a) you could do this. declare @a int select @a = (SELECT COUNT(*) FROM table) Now select the count value from the variable... select @a

Hi again, One thing I just noticed is you need a SPACE before the "WHERE clause" in the SQL example code you posted above. At the moment it dynamic SQL command looks like this. Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORMWhere Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME) Try adding a space as follows. I have also replaced the "||" with "+" if you would prefer this method of concatenation. declare @v_update_sql varchar(1000) declare @d_date char(23) select @d_date = '2009-03-02 12:46:00.000' declare @sql varchar(1000) declare @v_target_table_name varchar(100) Set @v_target_table_name = 'IM_DOC_TRANSFORM' Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + ' Where Last_Documented_Date_And_Time = CAST('''+@d_Date+''' AS DATETIME)' select @v_update_sql execute (@v_update_sql) The dynamic SQL should then look like this... Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORM Where Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME) See if that works for you. Cheers, Kevin

OK maybe this will help instead... Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = CAST('''||@d_Date||''' AS DATETIME)' If it does not work, do a "SELECT @v_update_sql" immediately after the "SET @v_update_sql..." statement and see what the dynamic SQL looks like with the date string included. You might see an obvious syntax error then perhaps. Cheers, Kevin

Hi jonhbt, Try this, you seem to be missing some qoutation makers on the CONVERT in your dynamic SQL which are necessary for a STRING to be concatenated. Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'''+@d_Date+''')' I hope this helps, Kevin

Hi cocoonwls, You will need to expand the query to include a WHERE clause. So something like this for example... SELECT t1.AID, t1.CODE, MAX(distinct(t1.TYPE) as t1Type), SUM(t1.Amount) as t1Amount, COALESCE(SUM(t2.Amount),0) FROM t1 left outer join t2 on t1.AID = t2.ID WHERE t1.DATE >= '20090101' AND t1.DATE <= '20090130' group by t1.AID,t1.CODE Cheers, Kevin

Hi Cocoonwis, You need to change the GROUP BY clause so it does not include the Amount fields. Try this instead... SELECT t1.AID, t1.CODE, MAX(distinct(t1.TYPE) as t1Type), SUM(t1.Amount) as t1Amount, COALESCE(SUM(t2.Amount),0) FROM t1 left outer join t2 on t1.AID = t2.ID group by t1.AID,t1.CODE Good luck, Kevin On Apr 9, 2009, at 9:17 AM, The Code Project forums wrote: Hi Kevin, Thanks for your help.I got it right now :) But i have another question about it, if the records are duplicated in t1, and also t2. How could i sum the amount in t1.Amount and t2.Amount. Example there are 2 record A1 in t1, then i would like to sum them.Also in t2, i would like to sum the amount which have the same id. I have try in my database, it dosen't SUM for me if i write like : SELECT t1.AID, t1.CODE, MAX(distinct(t1.TYPE) as t1Type), SUM(t1.Amount) as t1Amount, COALESCE(SUM(t2.Amount),0) FROM t1 left outer join t2 on t1.AID = t2.ID group by t1.AID,t1.CODE,t1.Amount,t2.Amount thanks in advance cocoonwls

Hi, If you know that table T1 has more records than table T2 you could do a LEFT OUTER JOIN as follows:- SELECT t1.AID,t1.CODE,t1.TYPE,t1.AMOUNT1,COALESCE(t2.AMOUNT2,0) FROM t1 LEFT OUTER JOIN t2 on (t1.AID = t2.ID) I hope this helps. Cheers, Kevin

Hi, Assuming the table is called "kh" and you want it ORDER BY Field1 you could try this. The syntax works with T-SQL (Sybase). You may need to amend it for your DB if different. SELECT t1.PriKey,t1.Field1, t1.Field2,t1.Field3,t1.Field4 FROM kh t1 INNER JOIN (SELECT MAX(Field1) AS Field1,Field4 FROM kh GROUP BY Field4) t2 ON (t1.Field4 = t2.Field4) WHERE t1.Field1 = t2.Field1 ORDER BY t1.Field1 I hope it helps. Cheers, Kevin

Hi I guess you could try something like this. CREATE PROCEDURE client_stuff @variable1 int = NULL, @variable2 varchar(10) = NULL, @variable3 double = NULL AS SELECT * FROM mytable t1 WHERE client_id = ISNULL(@variable1,t1.client_id) AND client_name = ISNULL(@variable2, t1.client_name) AND client_balance = ISNULL(@variable3, t1.client_balance) ... etc I hope that is the right syntax for your DB. Basically you pass in NULLABLE parameters which may or may not be specified, then in the select use ISNULL to replace any NULL values with the value in the column, so if NULL then give me everything in this column, otherwise give me only records whose value in the column match the parameter. I hope that helps, Cheers, Kevin

Hi there, I think you might want to use a CASE statement. Not sure which database system you are using but it would look something like this... SELECT column1, column2, CASE column3 WHEN 0 THEN 'Z' ELSE 'C' END FROM table1 Cheers, Kevin

Hi, It looks like the length is the issue. I guess you might have some tab characters in the code for formatting perhaps, not sure. Anyway, I just fixed a similar problem in a Sybase IQ procedure and just removed all the white space. So for example... DECLARE @SQLStatement varchar(1500) SET @SQLStatement = 'CREATE TABLE [' + @Surname + '] ([AutoID] [int] IDENTITY(1,1) NOT NULL,' + '[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_FName] DEFAULT (''''), ' + '[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''), ' + '[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname ... etc ... etc ... etc + ' STATISTICS_NORECOMPUTE = OFF, ' + ' IGNORE_DUP_KEY = OFF, ' + ' ALLOW_ROW_LOCKS = ON, ' + ' ALLOW_PAGE_LOCKS = ON)' + ' ON [PRIMARY]) ' + ' ON [PRIMARY]' EXEC @SQLStatement I hope that helps. Good luck :) , Kevin

Hi Kumar, SQL Server has about 150 different counters which can be enabled to measure system (database) performance. You can get lots of information regarding performancing monitoring and tuning including using counters at : www.sql-server-performance.com for example. You set counters to measure things like page faults per second or user connections etc etc Cheers, Kevin