Database

John Mecas wrote:

can anyone explain me this code

-- Alter an existing function called JoinBase
-- It takes an integer as a parameter called @JID
-- It will return a VARCHAR with a maximum size of 200 characters.
ALTER FUNCTION dbo.JoinBase(@JID INT) RETURNS VARCHAR(200)
AS 
BEGIN

-- Two local variables are declared
DECLARE @JEX VARCHAR(200),
@Qualification VARCHAR(20)

-- A cursor is declared. 
-- It will return the results of the SELECT statement one row for each loop
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
select enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
OPEN c1

-- Loops until the cursor has exhausted all the rows from the SELECT
WHILE (@@FETCH_STATUS = 0)
BEGIN

-- This line won't even compile because @Qual doesn't exist
-- but it is supposed to get the value of enb from the SELECT
-- statement into the local variable @Qual (@Qualification)
FETCH c1 INTO @Qual

-- If the fetch was successful
IF (@@FETCH_STATUS = 0)
BEGIN

-- Append @Qual into the existing list.
-- If the list doesn't exist create one as a blank string
-- The end result is a comma separated list mad up of each
-- @Qualification/@Qual returned from the query.
SET @JEX = IsNull(@JEX + ',', '') + @Qual
END
END

-- Close the cursor
CLOSE c1

-- Returns the result to the caller
RETURN @JEX
END

SET NOCOUNT ON
DECLARE @T1_id AS Int
DECLARE @T1_Name AS Varchar(10)
DECLARE @T2_id AS Int
DECLARE @T2_Reason AS Varchar(5)
DECLARE @qryResult AS Varchar(256)
DECLARE @cnt AS Smallint

IF NOT EXISTS(SELECT * FROM tempdb..sysobjects WHERE name LIKE '#T3[_]%') 
				CREATE TABLE #T3(id int, Name varchar(10), Reason1 varchar(5), 
				Reason2 varchar(5), Reason3 varchar(5), Reason4 varchar(5))varchar(5), Reason4 varchar(5))
DELETE FROM #T3

DECLARE CurT1 CURSOR FOR SELECT * FROM #t1
OPEN CurT1
FETCH CurT1 INTO @T1_id, @T1_Name
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #T3 (id, Name) VALUES(@T1_id, @T1_Name)
	SET @cnt = 0

	DECLARE CurT2 CURSOR FOR SELECT * FROM #t2 WHERE id = @T1_id
	OPEN CurT2
	FETCH CurT2 INTO @T2_id, @T2_Reason
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @cnt = @cnt + 1
		SET @qryResult =  'UPDATE #T3 SET Reason' + Cast(@cnt AS Varchar(10)) + ' = ''' 
			+ @T2_Reason + ''' WHERE id = ' + Cast(@T1_id As Varchar(5))
		EXEC (@qryResult)
		FETCH CurT2 INTO @T2_id, @T2_Reason
	END
	CLOSE CurT2
	DEALLOCATE CurT2
	FETCH CurT1 INTO @T1_id, @T1_Name
END
CLOSE CurT1
DEALLOCATE CurT1
SET NOCOUNT OFF
SELECT * FROM #T3
--SELECT * FROM #T1
--SELECT * FROM #T2

/*
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Shon')
insert into #T1 (id,Name) values (2,'Julie')

CREATE TABLE #T2(id int,Reason varchar(5))
insert into #T2 (id,Reason) values (1,'X')
insert into #T2 (id,Reason) values (2,'Y')
insert into #T2 (id,Reason) values (1,'Z')
insert into #T2 (id,Reason) values (1,'K')
*/

<br />
SqlCommand command = new SqlCommand("MyStoredProcedure", connection);<br />
command.CommandType = CommandType.StoredProcedure;<br />
object objData = command.ExecuteScalar();<br />
return objData;<br />

procedure_Insert.Parameters.Add(
                new System.Data.SqlClient.SqlParameter("@picture",
                System.Data.SqlDbType.VarBinary, -1,
                System.Data.ParameterDirection.Input,
                false, 0, 0, "picture", System.Data.DataRowVersion.Current, null)
                );