Database

Sunday Ironfoot wrote:

I would normally use Sprocs except it seems overkill for such a simple SQL SELECT statement.

public object Save()
{
    if (_bm == null)
    {
        return DBNull.Value;
    }
    else
    {
        MemoryStream ms = new MemoryStream();
        _bm.Save(ms,_bm.RawFormat);
        byte[] buf = ms.GetBuffer();
        ms.Close();
        return buf;
    }
}

SELECT
    StudentID,
    (SELECT Score FROM StudentScores 
     WHERE StudentId = ss.StudentId AND
           Course = 'Maths') AS Maths, 
    (SELECT Score FROM StudentScores 
     WHERE StudentId = ss.StudentId AND
           Course = 'English') AS English,
    (SELECT Score FROM StudentScores 
     WHERE StudentId = ss.StudentId AND
           Course = 'Hindi') AS Hindi
FROM
    StudentScores ss
ORDER BY 
    StudentID 

(ISNULL(SELECT MAX(Score) FROM StudentScores 
        WHERE StudentId = ss.StudentId AND
        Course = 'Maths'),0) AS Maths 

CREATE PROCEDURE GetGrades AS

DECLARE @sql VARCHAR(2500), @cClass VARCHAR(10) 

SET @sql = ''

DECLARE curClasses CURSOR FOR SELECT DISTINCT class FROM tblGrades
OPEN curClasses
FETCH NEXT FROM curClasses INTO @cClass
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = @sql + ', (SELECT grade 
                              FROM tblGrades t2 
                              WHERE t2.StudentId = t1.StudentId 
                                    and t2.Class = ''' + @cClass + ''') as ' + @cClass
	FETCH NEXT FROM curClasses INTO @cClass
END

close  curClasses
DEALLOCATE curClasses

SET @sql =  'SELECT studentid ' + @sql + ' FROM tblGrades t1 GROUP BY studentid'
exec(@sql)
GO

<br />
select cd.title, cd.serialnumber, cd.cdinfo, track.title, track.length, artist.name, artist.artistinfo<br />
from <br />
cd inner join cdtrack on cd.id = cdtrack.cdid <br />
inner join track on track.id = cdtrack.trackid<br />
inner join artisttrack on artisttrack.trackid = cdtrack.trackid<br />
inner join artist on artisttrack.artistid = artist.id<br />

SELECT *
FROM CD 
JOIN CDTrack ON CD.id = CDTrack.cdid

SELECT *
FROM CD
JOIN CDTrack ON CD.id = CDTrack.cdid
JOIN Track ON CDTrack.trackid = Track.id
JOIN ArtistTrack ON CDTrack.trackid = ArtistTrack.trackid
JOIN Artist ON ArtistTrack.artistid = Artist.id

FriendlySoluations wrote:

In other words I want ask if there is any way in which we will not install SQL Server2000 on client pc and the program will run properly?

CREATE PROCEDURE Ks_Ins_Media_Item
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@item int output
As
insert into Ks_Media_Item ([Description],Added_Date,Added_By)
values(@Desc,@added_date,@Added_by)
set @item = @@identity

CREATE PROCEDURE Ks_Ins_Media_Item_content
@Item_No int,
@content ntext,
@textlength int
As
insert into Ks_Media_Item_content(Item_no,content,text_length)
values(@item_no,@content,@textlength)

CREATE procedure ks_media_item_full_data
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@content ntext,
@textlength int
as
declare @ItemNo int
execute dbo.Ks_Ins_Media_Item @Desc, @added_date, @Added_by, @ItemNo output
execute dbo.Ks_Ins_Media_Item_content @ItemNo,@content, @textlength