October 10, 2011 at 4:13 pm
I have this stored procedure created in SQL SERVER2005 and I want to convert this store procedure to MsAccess and run it as individual query. I'm facing tremendous problem with Inner, Left and Right joins. Is there any tools available to convert stored procedure.I have seen convertion or database but only table conversion and not SP. Please let me know how to resolve this issue.
IF @flag='ST'
BEGIN
IF(@@ERROR = 0)
BEGIN
SET @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId);
SET @AUDIOSET = (SELECT TRIAL_MASTER.Audio_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId);
----- THIS IS WORD SET
IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'W%') AND @AUDIOSET IS NULL
BEGIN
Query 1: WOULD FETCH ME SINGLE ROW
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage,TM.Back_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
SELECT WORD AS WORD_BUFFER FROM Word_Upload_Master WHERE Isdelete='FALSE';
END
---- THIS IS WORD SET ALONG WITH AUDIO SET
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'W%')AND @AUDIOSET IS NOT NULL
BEGIN
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage,TM.Back_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
-- WORD BUFFER
SELECT WORD AS WORD_BUFFER FROM Word_Upload_Master WHERE Isdelete='FALSE';
--AUDIO TARGET
select tm.Audio_SetID,aum.AudioFileName,aum.AudioName,ttd.Task_Type_Detail_Name from
trial_master tm,task_type_details_settings ttds,task_type_details ttd,audio_upload_master aum where tm.audio_setID=ttds.setid and ttds.tdtypeid=ttd.tdtypeid and ttd.tdtypeid= aum.tdtypeid and ttds.audio_name=aum.audioname and tm.trial_settingid = @TrialId AND TM.Isdelete='FALSE';
--AUDIO BUFFER
SELECT AUDIOFILENAME AS AUDIO_BUFFER FROM Audio_Upload_Master WHERE Audio_Upload_Master.TDTypeID <> 30 AND Isdelete='FALSE';
END
-- THIS IS FOR ALPHABET SET
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'A%') AND @AUDIOSET IS NULL
BEGIN
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage,TM.Back_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
END
-- SET CONTAINS BOTH ALPHABET AND AUDIO SETS
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'A%') AND @AUDIOSET IS NOT NULL
BEGIN
-- SELECT THE ALPHABET
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage,TM.Back_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
-- SELECT THE AUDIO TARGET
Select tm.Audio_SetID,aum.AudioFileName,aum.AudioName,ttd.Task_Type_Detail_Name from
trial_master tm,task_type_details_settings ttds,task_type_details ttd,
audio_upload_master aum where tm.audio_setID=ttds.setid and ttds.tdtypeid=ttd.tdtypeid and ttd.tdtypeid= aum.tdtypeid and ttds.audio_name=aum.audioname and tm.trial_settingid = @TrialId AND TM.Isdelete='FALSE';
-- SELECT THE AUDIO BUFFER
SELECT AUDIOFILENAME AS AUDIO_BUFFER FROM Audio_Upload_Master WHERE Audio_Upload_Master.TDTypeID <> 30 AND Isdelete='FALSE';
END
-- THIS IS FOR NUMERIC SET
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'N%') AND @AUDIOSET IS NULL
BEGIN
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage, TM.Back_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId AND TM.Isdelete='FALSE';
END
-- THIS IS FOR BOTH NUMERIC AND AUDIO
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'N%')AND @AUDIOSET IS NOT NULL
BEGIN
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage, TM.Back_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
-- SELECT THE AUDIO TARGET SET
select tm.Audio_SetID,aum.AudioFileName,aum.AudioName,ttd.Task_Type_Detail_Name from
trial_master tm,task_type_details_settings ttds,task_type_details ttd,audio_upload_master aum where tm.audio_setID=ttds.setid and ttds.tdtypeid=ttd.tdtypeid and ttd.tdtypeid= aum.tdtypeid and ttds.audio_name=aum.audioname and tm.trial_settingid = @TrialId AND TM.Isdelete='FALSE';
-- SELECT THE AUDIO BUFFER
SELECT AUDIOFILENAME AS AUDIO_BUFFER FROM Audio_Upload_Master WHERE Audio_Upload_Master.TDTypeID <> 30 AND Isdelete='FALSE';
END
QUERY 3: WOULD FETCH ME 3 SET OF SELECT STATEMENT AND EACH CONTAINING SETS OF ROWS
--- THIS IS FOR THE SHAPES SET
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'S%') AND @AUDIOSET IS NULL
BEGIN
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage,TM.Back_Colour,TTDS.Shape_Colour_Code as Shape_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
SELECT Distinct(Substring(Task_Type_Detail_Name,3,len(Task_Type_Detail_Name))) as Shapes_Buffer FROM Task_Type_Details WHERE Isdelete='FALSE' AND TSettingID=1;
END
-- SELECT BOTH SHAPES AND AUDIO
ELSE IF @SETID IS NOT NULL AND @SETID =(SELECT TRIAL_MASTER.Visual_SetID FROM Trial_Master WHERE Trial_Master.Trial_SettingID = @TrialId AND Visual_SetID LIKE 'S%')AND @AUDIOSET IS NOT NULL
BEGIN
SELECT DISTINCT TM.Visual_SetID,TTDS.Shapes,IM.Instruction,(SM.F_Name + ' ' + SM.L_Name)AS NAME,TM.Task_Percentage AS Percentage,TM.Back_Colour,TTDS.Shape_Colour_Code as Shape_Colour,TTDS.Shape_Size,TM.Position,TM.Trial_Duration,TM.Inter_Interval_Duration,TM.No_Of_Trial
FROM Trial_Master as TM INNER JOIN Instruction_Master as IM ON TM.InstructionID=IM.InstructionID INNER JOIN Subject_Master AS SM ON TM.SubjectID=SM.SubjectID LEFT JOIN task_type_details_settings AS TTDS ON TM.Visual_SetID=TTDS.SetID INNER JOIN task_type_details TTD ON TTD.tdtypeid=TTDS.tdtypeid INNER JOIN task_setting_type AS TST ON TST.tsettingID= TTD.tsettingid WHERE TM.Trial_SettingID = @TrialId;
-- SELECT THE SHAPE BUFFER
SELECT Distinct(Substring(Task_Type_Detail_Name,3,len(Task_Type_Detail_Name))) as Shapes_Buffer FROM Task_Type_Details WHERE Isdelete='FALSE' AND TSettingID=1;
-- SELECT THE AUDIO TARGET
select tm.Audio_SetID,aum.AudioFileName,aum.AudioName,ttd.Task_Type_Detail_Name from
trial_master tm, task_type_details_settings ttds,task_type_details ttd, audio_upload_master aum where tm.audio_setID=ttds.setid and ttds.tdtypeid=ttd.tdtypeid and ttd.tdtypeid= aum.tdtypeid and ttds.audio_name=aum.audioname and tm.trial_settingid = @TrialId AND TM.Isdelete='FALSE';
--SELECT THE AUDIO BUFFER
SELECT AUDIOFILENAME AS AUDIO_BUFFER FROM Audio_Upload_Master WHERE Audio_Upload_Master.TDTypeID <> 30 AND Isdelete='FALSE';
END
-- THIS IS ONLY FOR THE AUDIO SET
ELSE
BEGIN
-- SELECT THE AUDIO TARGET
select tm.Audio_SetID,aum.AudioFileName,aum.AudioName,ttd.Task_Type_Detail_Name,
(SELECT IM.Instruction FROM Instruction_Master AS IM INNER JOIN Trial_Master AS TM ON IM.InstructionID=TM.InstructionID WHERE TM.Trial_SettingID=@TrialId) AS Instruction,(SELECT Back_Colour FROM TRIAL_MASTER WHERE Trial_SettingID=@TrialId) AS Back_Colour,(SELECT Position FROM TRIAL_MASTER WHERE Trial_SettingID=@TrialId) AS Position,(SELECT Trial_Duration FROM TRIAL_MASTER WHERE Trial_SettingID=@TrialId) AS Trial_Duration,(SELECT Inter_Interval_Duration FROM TRIAL_MASTER WHERE Trial_SettingID=@TrialId) AS Inter_Interval_Duration,(SELECT No_Of_Trial FROM TRIAL_MASTER WHERE Trial_SettingID=@TrialId) AS No_Of_Trial from trial_master tm, task_type_details_settings ttds,task_type_details ttd,audio_upload_master aum
Where tm.audio_setID=ttds.setid and ttds.tdtypeid=ttd.tdtypeid and ttd.tdtypeid= aum.tdtypeid and ttds.audio_name=aum.audioname and tm.trial_settingid = @TrialId AND TM.Isdelete='FALSE';
--SELECT THE AUDIO BUFFER
SELECT AUDIOFILENAME AS AUDIO_BUFFER FROM Audio_Upload_Master WHERE Audio_Upload_Master.TDTypeID <> 30 AND Isdelete='FALSE';
END
END
END
October 10, 2011 at 4:35 pm
banerji.uddipto (10/10/2011)
I have this stored procedure created in SQL SERVER2005 and I want to convert this store procedure to MsAccess and run it as individual query. I'm facing tremendous problem with Inner, Left and Right joins. Is there any tools available to convert stored procedure.I have seen convertion or database but only table conversion and not SP. Please let me know how to resolve this issue.
The problem is... you can't. It's just too complex to convert directly, and uses too many tools of T-SQL. You'd probably have to wrap this in DAO coding under a form and use multiple query/views to get this logic to behave. This isn't just a syntax issue.
I'll admit I'm not up on the current versions of MSAccess, but this being directly transaltable into a single query would be a major departure from what I remember of the architecture.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 11, 2011 at 7:54 am
The other question is WHY would you want to take something out of sql server and move it to Access??? You are going backwards on performance and scalability.
As Kraig said, there is too much complexity for a single query in Access to do this. An Access query is NOT t-sql and just does not have all the conditional processing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2011 at 11:25 am
Answering this is likely not going to get me anywhere, but anyway...
I agree with everyone else... There's just no way around it: T-SQL is a LOT more flexible and powerful than ANYTHING in Access. What are you using this query for? You can use ADO and pass parameters etc to the stored procedure that's being called in SQL Server and return the results to a recordset in Access... You can base a report on the stored procedure if you want, too. So what exactly are you trying to do? If you want to collect parameter values from the user from an Access form, that's fairly trivial too, if you do it inside your form's code...
The whole thing about inner and outer joins is a red herring. The only type of join that Access does not natively support is FULL OUTER JOIN.
Okay, that said... Use SQL Server for what it's best at - querying huge datasets. Send as little data to Access as possible. Sounds like you're going about this from the wrong angle.
October 12, 2011 at 11:38 am
+1 on the going backward point.
But for readability's sake :
IF @flag = 'ST'
BEGIN
IF ( @@ERROR = 0 )
BEGIN
SET @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
) ;
SET @AUDIOSET = (
SELECT
TRIAL_MASTER.Audio_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
) ;
----- THIS IS WORD SET
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'W%'
)
AND @AUDIOSET IS NULL
BEGIN
--Query 1: WOULD FETCH ME SINGLE ROW
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' ' + SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
SELECT
WORD AS WORD_BUFFER
FROM
Word_Upload_Master
WHERE
Isdelete = 'FALSE' ;
END
---- THIS IS WORD SET ALONG WITH AUDIO SET
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'W%'
)
AND @AUDIOSET IS NOT NULL
BEGIN
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' ' + SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
-- WORD BUFFER
SELECT
WORD AS WORD_BUFFER
FROM
Word_Upload_Master
WHERE
Isdelete = 'FALSE' ;
--AUDIO TARGET
select
tm.Audio_SetID
, aum.AudioFileName
, aum.AudioName
, ttd.Task_Type_Detail_Name
from
trial_master tm
, task_type_details_settings ttds
, task_type_details ttd
, audio_upload_master aum
where
tm.audio_setID = ttds.setid
and ttds.tdtypeid = ttd.tdtypeid
and ttd.tdtypeid = aum.tdtypeid
and ttds.audio_name = aum.audioname
and tm.trial_settingid = @TrialId
AND TM.Isdelete = 'FALSE' ;
--AUDIO BUFFER
SELECT
AUDIOFILENAME AS AUDIO_BUFFER
FROM
Audio_Upload_Master
WHERE
Audio_Upload_Master.TDTypeID <> 30
AND Isdelete = 'FALSE' ;
END
-- THIS IS FOR ALPHABET SET
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'A%'
)
AND @AUDIOSET IS NULL
BEGIN
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' ' + SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
END
-- SET CONTAINS BOTH ALPHABET AND AUDIO SETS
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'A%'
)
AND @AUDIOSET IS NOT NULL
BEGIN
-- SELECT THE ALPHABET
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' ' + SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings
AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
-- SELECT THE AUDIO TARGET
Select
tm.Audio_SetID
, aum.AudioFileName
, aum.AudioName
, ttd.Task_Type_Detail_Name
from
trial_master tm
, task_type_details_settings ttds
, task_type_details ttd
, audio_upload_master aum
where
tm.audio_setID = ttds.setid
and ttds.tdtypeid = ttd.tdtypeid
and ttd.tdtypeid = aum.tdtypeid
and ttds.audio_name = aum.audioname
and tm.trial_settingid = @TrialId
AND TM.Isdelete = 'FALSE' ;
-- SELECT THE AUDIO BUFFER
SELECT
AUDIOFILENAME AS AUDIO_BUFFER
FROM
Audio_Upload_Master
WHERE
Audio_Upload_Master.TDTypeID <> 30
AND Isdelete = 'FALSE' ;
END
-- THIS IS FOR NUMERIC SET
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'N%'
)
AND @AUDIOSET IS NULL
BEGIN
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' ' + SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings
AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId
AND TM.Isdelete = 'FALSE' ;
END
-- THIS IS FOR BOTH NUMERIC AND AUDIO
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'N%'
)
AND @AUDIOSET IS NOT NULL
BEGIN
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' ' + SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master
as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings
AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type
AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
-- SELECT THE AUDIO TARGET SET
select
tm.Audio_SetID
, aum.AudioFileName
, aum.AudioName
, ttd.Task_Type_Detail_Name
from
trial_master tm
, task_type_details_settings ttds
, task_type_details ttd
, audio_upload_master aum
where
tm.audio_setID = ttds.setid
and ttds.tdtypeid = ttd.tdtypeid
and ttd.tdtypeid = aum.tdtypeid
and ttds.audio_name = aum.audioname
and tm.trial_settingid = @TrialId
AND TM.Isdelete = 'FALSE' ;
-- SELECT THE AUDIO BUFFER
SELECT
AUDIOFILENAME AS AUDIO_BUFFER
FROM
Audio_Upload_Master
WHERE
Audio_Upload_Master.TDTypeID <> 30
AND Isdelete = 'FALSE' ;
END
--QUERY 3: WOULD FETCH ME 3 SET OF SELECT STATEMENT AND EACH CONTAINING SETS OF ROWS
--- THIS IS FOR THE SHAPES SET
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'S%'
)
AND @AUDIOSET IS NULL
BEGIN
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' '
+ SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Colour_Code as Shape_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master
as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master
AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings
AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type
AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
SELECT Distinct
( Substring(Task_Type_Detail_Name , 3 , len(Task_Type_Detail_Name)) ) as Shapes_Buffer
FROM
Task_Type_Details
WHERE
Isdelete = 'FALSE'
AND TSettingID = 1 ;
END
-- SELECT BOTH SHAPES AND AUDIO
ELSE
IF @SETID IS NOT NULL
AND @SETID = (
SELECT
TRIAL_MASTER.Visual_SetID
FROM
Trial_Master
WHERE
Trial_Master.Trial_SettingID = @TrialId
AND Visual_SetID LIKE 'S%'
)
AND @AUDIOSET IS NOT NULL
BEGIN
SELECT DISTINCT
TM.Visual_SetID
, TTDS.Shapes
, IM.Instruction
, ( SM.F_Name + ' '
+ SM.L_Name ) AS NAME
, TM.Task_Percentage AS Percentage
, TM.Back_Colour
, TTDS.Shape_Colour_Code as Shape_Colour
, TTDS.Shape_Size
, TM.Position
, TM.Trial_Duration
, TM.Inter_Interval_Duration
, TM.No_Of_Trial
FROM
Trial_Master as TM
INNER JOIN Instruction_Master
as IM
ON TM.InstructionID = IM.InstructionID
INNER JOIN Subject_Master
AS SM
ON TM.SubjectID = SM.SubjectID
LEFT JOIN task_type_details_settings
AS TTDS
ON TM.Visual_SetID = TTDS.SetID
INNER JOIN task_type_details TTD
ON TTD.tdtypeid = TTDS.tdtypeid
INNER JOIN task_setting_type
AS TST
ON TST.tsettingID = TTD.tsettingid
WHERE
TM.Trial_SettingID = @TrialId ;
-- SELECT THE SHAPE BUFFER
SELECT Distinct
( Substring(Task_Type_Detail_Name , 3 , len(Task_Type_Detail_Name)) ) as Shapes_Buffer
FROM
Task_Type_Details
WHERE
Isdelete = 'FALSE'
AND TSettingID = 1 ;
-- SELECT THE AUDIO TARGET
select
tm.Audio_SetID
, aum.AudioFileName
, aum.AudioName
, ttd.Task_Type_Detail_Name
from
trial_master tm
, task_type_details_settings ttds
, task_type_details ttd
, audio_upload_master aum
where
tm.audio_setID = ttds.setid
and ttds.tdtypeid = ttd.tdtypeid
and ttd.tdtypeid = aum.tdtypeid
and ttds.audio_name = aum.audioname
and tm.trial_settingid = @TrialId
AND TM.Isdelete = 'FALSE' ;
--SELECT THE AUDIO BUFFER
SELECT
AUDIOFILENAME AS AUDIO_BUFFER
FROM
Audio_Upload_Master
WHERE
Audio_Upload_Master.TDTypeID <> 30
AND Isdelete = 'FALSE' ;
END
-- THIS IS ONLY FOR THE AUDIO SET
ELSE
BEGIN
-- SELECT THE AUDIO TARGET
select
tm.Audio_SetID
, aum.AudioFileName
, aum.AudioName
, ttd.Task_Type_Detail_Name
, (
SELECT
IM.Instruction
FROM
Instruction_Master
AS IM
INNER JOIN Trial_Master
AS TM
ON IM.InstructionID = TM.InstructionID
WHERE
TM.Trial_SettingID = @TrialId
) AS Instruction
, (
SELECT
Back_Colour
FROM
TRIAL_MASTER
WHERE
Trial_SettingID = @TrialId
) AS Back_Colour
, (
SELECT
Position
FROM
TRIAL_MASTER
WHERE
Trial_SettingID = @TrialId
) AS Position
, (
SELECT
Trial_Duration
FROM
TRIAL_MASTER
WHERE
Trial_SettingID = @TrialId
) AS Trial_Duration
, (
SELECT
Inter_Interval_Duration
FROM
TRIAL_MASTER
WHERE
Trial_SettingID = @TrialId
) AS Inter_Interval_Duration
, (
SELECT
No_Of_Trial
FROM
TRIAL_MASTER
WHERE
Trial_SettingID = @TrialId
) AS No_Of_Trial
from
trial_master tm
, task_type_details_settings ttds
, task_type_details ttd
, audio_upload_master aum
Where
tm.audio_setID = ttds.setid
and ttds.tdtypeid = ttd.tdtypeid
and ttd.tdtypeid = aum.tdtypeid
and ttds.audio_name = aum.audioname
and tm.trial_settingid = @TrialId
AND TM.Isdelete = 'FALSE' ;
--SELECT THE AUDIO BUFFER
SELECT
AUDIOFILENAME AS AUDIO_BUFFER
FROM
Audio_Upload_Master
WHERE
Audio_Upload_Master.TDTypeID <> 30
AND Isdelete = 'FALSE' ;
END
END
END
October 13, 2011 at 4:46 am
I have a dissenting opinion. VBA in Access has its share of quirks and bad design decisions but it has generally more capabilities. T-SQL is a sad 60's era single pass mess that barely ranks being called a "compiler." Even Microsoft realises this, they've went so far as to add in the ability to call dot net in SQL server now to allow for a proper programming language on the server. While ANSI SQL (and a few nifty Microsoft extensions) are good for set based operations, T-SQL is pretty much a joke when used for implementing even the simplest algorithms.
October 13, 2011 at 7:26 am
patrickmcginnis59 (10/13/2011)
I have a dissenting opinion. VBA in Access has its share of quirks and bad design decisions but it has generally more capabilities. T-SQL is a sad 60's era single pass mess that barely ranks being called a "compiler." Even Microsoft realises this, they've went so far as to add in the ability to call dot net in SQL server now to allow for a proper programming language on the server. While ANSI SQL (and a few nifty Microsoft extensions) are good for set based operations, T-SQL is pretty much a joke when used for implementing even the simplest algorithms.
Comparing VBA and t-sql is just not the same thing. t-sql is WAY more powerful than the query engine in Access. VBA would be the programming language that can interface with Access. Dot net would be the programming language for sql server. I think i would prefer to have the high speed data access and way more powerful dot net programming from sql server.
Really Access was designed to be both a database and a front end. Unfortunately it doesn't do either of them very well. SQL server was designed to be a database only and it does that very well. It just simply is not intended to be a programming tool.
I guess we will have to agree to disagree on this one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 13, 2011 at 8:02 am
VBA has much better syntax and a more sophisticated parser. T-SQL's main advantage is that its server side and closer to the better DBMS (SQL Server versus JET), but other than that it seems very primitive to me. I don't consider either VBA or T-SQL to be a query engine so I can't really answer to that.
October 13, 2011 at 8:14 am
tsql <> query?
Who the heck are you kidding with this?
October 13, 2011 at 8:19 am
You do know what VBA is? Visual Basic for Applications. It is scaled down version of VB6 for MS Office Applications. It has nothing to do with queries. Like every other programming language it can retrieve data from a database by calling a method of some sort of data access.
http://en.wikipedia.org/wiki/Visual_Basic_for_Applications
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 13, 2011 at 8:46 am
Ninja's_RGR'us (10/13/2011)
tsql <> query?Who the heck are you kidding with this?
Sure, if you want to consider SQL Server as one big blob of program then thats fine, I'm not going to disuade you from that. If SQL Server is T-SQL in your mind then of course, just like if you think VBA and Acess with JET is the same thing.
Sure, I wouldn't hire you either, but thats probably outside the scope of this discussion 😛
October 13, 2011 at 8:53 am
patrickmcginnis59 (10/13/2011)
Ninja's_RGR'us (10/13/2011)
tsql <> query?Who the heck are you kidding with this?
Sure, if you want to consider SQL Server as one big blob of program then thats fine, I'm not going to disuade you from that. If SQL Server is T-SQL in your mind then of course, just like if you think VBA and Acess with JET is the same thing.
Sure, I wouldn't hire you either, but thats probably outside the scope of this discussion 😛
That's crossing the line between different thinking and unprofessional.
October 13, 2011 at 9:15 am
OK... gotta add my 2 cents.
To the Original poster - someone else has already mentioned calling the stored proc using dao/ado in Access. The sp you post COULD be translated into MSAccess, but it would probably involve using VBA and queries using queries that use queries. Big Mess.
To the Access/SQL debate - they both have DB engines but BIG differences in capacities. Access also has presentation tools that SQL doesn't without the CLR integration. They are both adequate tools for their target user groups.... and Access makes a pretty good front end to SQL Server's DB engine - IMHO.
EDIT -
OH... and as far as VBA being a scaled down VB6... you can add the VB6 extension library AND you can call windows API functions directly. That's actually pretty powerful.
October 13, 2011 at 9:18 am
Uripedes Pants (10/13/2011)
OK... gotta add my 2 cents.To the Original poster - someone else has already mentioned calling the stored proc using dao/ado in Access. The sp you post COULD be translated into MSAccess, but it would probably involve using VBA and queries using queries that use queries. Big Mess.
To the Access/SQL debate - they both have DB engines but BIG differences in capacities. Access also has presentation tools that SQL doesn't without the CLR integration. They are both adequate tools for their target user groups.... and Access makes a pretty good front end to SQL Server's DB engine - IMHO.
SQL also has a powerful presentation tool.. It's called SSRS and afaik it's at least equivalent to access if it doesn't beat the crap out of it (UI wise).
October 13, 2011 at 9:22 am
SSRS is only equivalent to Access reports - and from what I've done so far, it's kind of clunky but that's probably my lack of experiance.
SQL Server has no equivalent to Access forms - unless I'm really missing something 😀
not arguing or lapsing into unprofessional comments as someone else did, just putting out my opinion that they are very different products but actually both are pretty good at what they were intended for.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply