Microsoft SQL Server: Programming | Forum profile

You can't return FIELD from a query, you can return variable: CODE CREATE PROCEDURE [dbo].[Countit] AS BEGIN SET NOCOUNT ON; DECLARE @CountOfID int SELECT @CountOfID = COUNT(CompanyDetailID) FROM mainCompanyDetail WHERE Deleted = 0 return @CountOfID END Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

Pleas, post some example data for all tables and what you want as a result. Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

You definitely don't want one database connection for each user. If I recall my ASP.NET programming (which I haven't done in a while), it would be best to create an SQL login for the web service application - that way the connection string is always identical. I believe that ADO.NET will cache connections if the connection strings are identical - and because of that you would simply open and ...

Please see this example How to insert information into multiple related tables and return ID using SQLDataSource

CODE SELECT Client, SUM(Trans) AS Trans FROM Clients WHERE DATEDIFF(hh, TransactionDate, GETDATE()) < 49 GROUP BY Client HAVING SUM(Trans) > = 400 NOT TESTED! Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

Table has End Date, but did the table has START date? If so, then just pass them as parameters to the function: CODE SELECT ...., dbo.GetBusinessDays(StartDateColumn, EndDateColumn) AS WorkingDays FROM TheTable Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

You can not use table variable, because it is not visible in the dynamic SQL. Try this: CODE create table #MyID( corekey numeric (13), coreid char (10), coretype char (10), corecomm varchar (4000), Id int ) INSERT INTO @ID (corekey,coreid,corecomm, Id) SELECT corekey,coreid,corecomm, , ROW_NUMBER() OVER ( PARTITION BY coreid ORDER BY corecomm) FROM dbo.core_table...

try Single quotes CODE bpbeltyp='B'

CODE ;with cte_ProvidersSummary as (select sum(c.pat_amt) as PatBal, replace(ltrim(replace(pat.med_rec_nbr, '0', ' ')), ' ', '0') as Chart#, lp.desc_30 as Provider from charges c inner join lrdc_providers lp on lp.provider_id = c.rendering_id inner join patient pat on pat.person_id = c.person_id inner join person per on per.person_id = pat.person_id where c.pat_amt >...

You will need to do a LEFT JOIN between your supplier table and the orderdetail table to enable you to get all the records from the supplier table even if they havent got any orders against them. Example of LEFT JOIN syntax: http://www.w3schools.com/sql/sql_join_left.asp You will then need to as you said: 'orderqty' * 'unitcost' to get a line total finally you will need to look at GROUP ...

CODE ;with cte_ProvidersSummary as (select sum(c.pat_amt) as PatBal, replace(ltrim(replace(pat.med_rec_nbr, '0', ' ')), ' ', '0') as Chart#, lp.desc_30 as Provider from charges c inner join lrdc_providers lp on lp.provider_id = c.rendering_id inner join patient pat on pat.person_id = c.person_id inner join person per on per.person_id = pat.person_id where c.pat_amt >...

Pleas, post some example data for all tables and what you want as a result. Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

CODE SELECT I.ItemID FROM ITEM I inner join ItemDetails ID on I.ItemID = ID.ItemID where (ID.ItemDescription like ' %sara%' or ID.ItemDescription like '%davidson%') group by I.ItemID having count(distinct(ID.ItemDetailsID)) > =2 One of the possible solutions.

have you tried the CREATE TABLE statement? r937.com | rudy.ca Buy my new book Simply SQL from Amazon

What is the difference? Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

Select attrid, attrype , case when attrcomm='reading' then attrcomm else '' end attrcomm1 , case when attrcomm='automobiles' then attrcomm else '' end attrcomm2 from @AttrHobby

You used [ instead of ( e.g. select count ( *) from ...

The error is not on DECLARE it in query row: This: CODE SELECT col1, @TOTALPEOPLE, @TOTALMENFROM TABLE1 should be CODE SELECT col1, @TOTALPEOPLE, @TOTALMENFROM FROM TABLE1 Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

CODE SELECT Client, SUM(Trans) AS Trans FROM Clients WHERE DATEDIFF(hh, TransactionDate, GETDATE()) < 49 GROUP BY Client HAVING SUM(Trans) > = 400 NOT TESTED! Borislav Borissov VFP9 SP2, SQL Server 2000/2005.

Have you tried creating a (temporary) table of values 1 - 31? Or possibly create on the fly for number of days in the month - see FAQ for finding last day of month. Then join to your query in a way that all values of the "day" table are used (right join). Just thoughts. Good Luck, djj The Lord is My Shepard (Psalm 23) - I need someone to lead me!