Database

Hi, don't know what is ur actual doubt because the problem stated sarang_k wrote: How to use the Case in stored procedure is a normal thing. If you are looking for syntax there is plenty in google with examples too. However, I have given 2 sample for the same program Sample 1: Create procedure TestCaseStatement as Begin declare @tbl table(id int) insert into @tbl select 0 union all select 1 union all select 2 union all select 1 union all select 1 select id ,case when id = 1 then 'One' else 'Other Number' **end as Value** from @tbl End Sample 2: Create procedure TestCaseStatement as Begin declare @tbl table(id int) insert into @tbl select 0 union all select 1 union all select 2 union all select 1 union all select 1 select id ,**Value =** case when id = 1 then 'One' else 'Other Number' end from @tbl End The output id Value 0 Other Number 1 One 2 Other Number 1 One 1 One As you can see that I have shown 2 ways of holding the case result into a pseudo column. And as Mr. Mycroft stated, if you have any specific doubt, please don't hesitate to share it here. We all would love to help you. Hope you understand and ur doubt as per the question title has been answered. Have a nice day :) Niladri Biswas

Try this declare @tbl table(id int identity, refid int) insert into @tbl select 0 union all select 1 union all select 2 union all select 1 union all select 1 select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total from @tbl t left join ( select refid,count(refid) as cnt from @tbl where refid <> 0 group by refid having (count(refid)>0)) x on t.id = x.refid Output : id refid total 1 0 3 2 1 1 3 2 0 4 1 0 5 1 0 :) Niladri Biswas

Hi, coming to ur question deepseeindeepsy wrote: Is it possible to fetch it? Ans : Yes I am giving a small example Step 1: Create the Stored Proc Alter Procedure GetMultipleRecords As Begin -- First select Select \* from test1 -- Second select Select \* from test2 End So this will give u two record sets Step 2: Use Data Adapter & DataSet From Asp.net application, by using DataAdapter get the records set into the DataSet Step 3: Use respective datatable(s) from DataSet Get the record sets from the DataSet into DataTable(some pseudo code in c#) e.g. DataSet ds = getDataRecords();->You will get the records from database if(ds!=null && ds.tables.count > 0) { //Store the result for Select * from test1 into datatable firstDt DataTable firstDt = ds.tables\[0\]; //Store the result for Select * from test2 into datatable secondDt DataTable secondDt = ds.tables\[1\]; } Hope you get the idea. Now try by urself and I am sure you will reach the point. Have a nice day. :) Niladri Biswas

David, I am required to create a report whereby each item quantity has it's own line (for jotting notes or whatever next to each. I love my Sales Dept!!!) In any event, I see where you are going with that, but I won't use a cursor. There is a niffty loop that I found that takes the place of using cursors which I have found is alot lighter. I think that is a plan and I'll give it a go. Thanks to all of you for your time with my issue, which is certainly not yours. I appreciate it.

Good Day in have Resolved it by adding right('0'+convert(varchar(2),datepart(hour,tb.starttime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.starttime)),2) AS[Start Time], right('0'+convert(varchar(2),datepart(hour,tb.endtime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.endtime)),2) AS [End Time], Thanks Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Sorry to bother you again.Can you please get me some links in getting this specific problem.I seem to get a general database copy procedures. Many thanks for your help.

This may help a) http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column-sql-server[^] b) http://www.mssqltips.com/tip.asp?tip=1397[^] :) Niladri Biswas

Tuning a database is an art and there is no 1 silver bullet, you can only try a number of things and see if they are worth the changes. For a start the size of your indexes should not concern you, on a heavily indexed table this is not unusual. If you insert is a bacth job (with 100k records is should be) then it may be worth dropping the indexes for the insert and recreating them. This is a DRASTIC measure and should be tested for cost on another server. Consider the changes to the clustered index suggested Consider moving the indexes to another physical drive Partitioning your data (think archiving but you can query across partitions) These are but some of your options, I assume you have use query profiler to asses the cost of your queries.

thanks a lot its working

Thanks so much it really works for me.What i want to find out now is How can i make sure that the copy process doesnt copy data already copied when doing it on another session. If the copy is done onece during the second operation will it know what records to add? How do you also solve the problem when the two databases run in Different virtual private networks? Lastly,when you copy the data it doesnt really check what has been copied before.So all the information will be be copied again every time the code runs. Many thanks modified on Wednesday, November 11, 2009 7:51 AM

Try this declare @str as varchar(100) declare @startposition int declare @endposition int declare @stopingcondition int declare @delimeter char(1) set @startposition = 0 set @stopingcondition = 15 set @delimeter ='' set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data --Program starts ;with num_cte as ( select 1 as rn union all select rn +1 as rn from num_cte where rn <= len(@str) ) , get_all_delimited_char_pos_cte as ( select row_number()over(order by rn) cnt ,rn,chars from num_cte cross apply( select substring(@str,rn,1) AS chars) splittedchars where chars = @delimeter ) select @endposition = rn from get_all_delimited_char_pos_cte where cnt = @stopingcondition select SUBSTRING(@str,@startposition,@endposition) as First15thWords Output: First15thWords I would like to get first 15 word from a long sentence but not sure The approach: If you do a substring like declare @str as varchar(100) set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data select SUBSTRING(@str,0,68) you will get the first 15 words. So the challenge was to get the number 68(count by youself and you will find that) By using the get_all_delimited_char_pos_cte CTE and with the help of cross apply I am able to get the position of the delimited characters and from there I just picked up the position of the 15th occurrence of the delimited charecter Hope this helps :) Niladri Biswas modified on Wednesday, November 11, 2009 7:58 AM

Give a try with this... I made this based on my understanding Declare @GetDBNames sysname Declare @DynSql nvarchar(max) declare @result table ([Database_Name] nvarchar(128), [Stored Procedure Name] sysname) Declare DBNames cursor for Select '['+name+']' from master.dbo.sysdatabases open DBNames FETCH NEXT FROM DBNames into @GetDBNames WHILE @@FETCH_STATUS=0 BEGIN SET @DynSql = ' Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name'' From '+ @GetDBNames+'.Information_Schema.Routines ' insert @result exec sp_executesql @DynSql FETCH NEXT FROM DBNames into @GetDBNames END Close DBNames Deallocate DBNames select * from @result It gives me all the stored procs names pertaining to the databases. Hope this helps. :) Niladri Biswas

Well I would say you can take your bits and ..... I would not even consider the bit type solution, purely from a downstream support aspect. Imagine coming in to support such a system. I think the table solution is going to be more flexible and supportable and will be more queriable by your users (who probably have no idea what a bit is). I would have 2 tables, Dates and Times, allow the user to enter as many times for a day as they require, supply a propagate forward by week/month methods. This can then interact with a calendar control.

So do I as a rule, but I remembered it from before ADO.NET, when theree were no such things as datasets. Also useful as datareaders are faster than datasets. Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

Vuyiswa Maseko wrote: This means that every time i do a restore i need to copy the filer over and if the Servers are not in the same place and the backup is big this can be a lengthy process. That's probably also the reason why you cannot restore from a network-drive. It would be slow, since every read would be reading from a remote drive over TCP/IP. I guess that it's faster to download the really big file and restore from a local drive, then try to restore from a network path (which may temporarily loose connectivity!) The software refuses to use a network path. Solutions mean either changing the software, or the network path. Out of those two options, the path is probably the easier on to change. I are Troll :suss:

It is possible that you do not have the WMI service running. To activate the WMI service, go to 'add or remove programs' and then into turn on/off windows features. Alternatvely there are issues if you have not cleanly uninstalled and earlier version. Mark Michaelis' Weblog[^] has an interesting article on this that may help sole your problem. If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk

Physical database design means what exactly you want to do...?