קריאה לSTORED PROCEDURES מתוך SELECT ב(TSQL)SQL SERVER 2005

[Ghosthunter]

שלום,

אני מעוניין לקרוא לSTORED PROCEDURES מתוך SELECT בSQL SERVER 2005.

הסבר מעמיק יותר - יש לי פרוצדורה שבודקת האם יש גישה למשתמש מסויים. אני רוצה להחזיר את כל הרשומות שיש למשתמש גישה אליהן(לדוגמא כל ההודעות שהוא יכול לקרוא).

ניסיתי דבר כזה:

select * 
from table
where exec myProc @user,table.id = 1

אבל זה לא התחביר(אני מקבל שגיאה).

רציתי לדעת מה התחביר שמאפשר את זה?

תודה רבה,

אופיר.

[NJorl]

נתחיל מה- Stored Procedures:

אלה יכולים להחזיר Results Set כלומר טבלה או Scalar כלומר מספר יחיד בד"כ שלם

אני עדיין לא הבנתי מה בדיוק אתה מנסה לעשות

מה הפרוצדורה myProc עושה ?

אל תשכח גם שהתחביר של where צריך להיות מורכב בסופו של דבר מ-אופרנטור דו צדדי שמשני צידיו יש אופרנדים

בדוגמא שנתת זה לא מתקיים כי כאשר ה- parser ישלים את הפרוצדורה הפנימית יתקבל ביטוי כמו

where 1

ועם זה המכונה לא יודעת מה לעשות

אם זה עוזר במשהו אולי אתה יכול לעשות זאת בשלבים , משהו כזה ?

DECLARE	@return_value int

EXEC	@return_value = myProc @user
select * from table where something = @return_value 

[Ghosthunter]

myProc עושה משהו כזה:

declare b1 bit;
declare b2 bit;
select @b1 = can_do something1, @b2 = can_do_something2
from tbl_auth
where user = @user_Id and id = @id

if b1 = 1 or b2 = 1
  return 1
else
  return 0

כמובן שזה קצת יותר איפים ולוגיקה קצת יותר מסובכת, אך זה העיקרון.

אם תשים לב, הWHERE שלי מורכב מקריאה לפרוצדורה הזו ו= 1.

גיליתי שאם אני לא רושם את הEXEC, הוא מצפה לפונקציה. אין דרך להגיד לו שאני רוצה לקחת את הערך שחזר מהפרוצדורה?

בקשר לשלבים - יש לי כמה רשומות שאני רוצה לבדוק האם יש למשתמש הרשאות לגביהן. בדרך שהצגת זאת, אני אצטרך לבצע לולאה כלשהי עבור כל רשומה. אני לא יודע אם זה יעיל כל כך או אפשרי בכלל.

הסבר קצת יותר מעמיק על מה שאני רוצה לעשות - אני בונה פורום. בכל פורום יש הודעות שהמנהל החליט להסתיר אותן כתוצאה מהפרה של כללי הפורום. אני שומר עבור כל הודעה שכזו האם המנהל החליט להסתיר אותה או לא. הפרוצדורה שבודקת האם יש הרשאות למשתמש בודקת האם המשתמש הוא מנהל, או שייך לקבוצת המודריטורים של אותו פורום. אם כן, המשתמש יכול לראות את ההודעה. אם לא, והמנהל החליט להסתיר את ההודעה, הוא לא יכול לראות.

אני מעוניין כעת להחזיר למשתמש את כל ההודעות שהוא יכול לקרוא ע"י שימוש בפרוצדורה הזו ושימוש בSELECT עם WHERE EXEC canread ... = 1.

לא הצלחתי לעשות זאת עם הכנסת הEXEC לתוך הWHERE, וגם לא הצלחתי לעשות זאת ע"י הדבר הבא:

select * 
from table1,
(select *, exec myProc @user, tbl2.id as can from table1 as tbl2) as table2
where 
table2.can = 1 and table1.id = table2.id

יש לך עצות כשהן ע"מ להתגבר על הבעיה הזו?

תודה.

[NJorl]

המממ, אני חושב שמה שיכול לפתור לך את הבעיה זה השימוש ב- SQL Functions

ההצהרה הכללית דומה מאוד ל- Stored Procedures

create function fn_test1
(
@user_id varchar(50)
)
return int
as
begin
-- put your t-sql code here
end

כלומר זה במקום הפרוצדורה הפנימית החיצונית נשארת פרוצדורה

דרך אחרת לגמרי ואני חושב הרבה יותר טרוויאלית ופשוטה:

הרי אתה בטח מנהל שם טבלת משתמשים, טבלת קשר הרשאות משתמשים, ובטבלה של ההודעות עצמן יש שדה סטאטוס רשומה

אם אתה מבצע חיתוך בין שלושת הטבלאות כל משתמש יקבל את הרשומות שהוא רשאי לצפות בהן

הפתרון מתאים גם אם ברשומות אתה מחזיק שדה יותר מורכב מרק בולאני , מוסתר , לא מוסתר, איזשהו int עם כמה סטאטוסים

כאשר בטבלת הקשר בין משתמשים לסטאטוסים אתה פשוט מנהל לכל משתמש אילו סטאטוסים הוא רשאי לצפות.

שני inner join - ים וזה הכל.

[Ghosthunter]

יכול להיות שאני אמצא דרך לעשות INNER JOIN. הבעיה היא שזה קצת יותר מסובך מ- 3 טבלאות. בכל מקרה, השאלה היא האם אני יכול להשתמש בPROCEDURES בתוך SELECT. הבנתי שהן יותר יעילות מפונקציות.

אם בכל זאת אחליט להעביר הכל לפונקציות - אילו פרוצדורות כדאי לי להמיר? רק אלה שאני משתמש בהן בתוך WHERE, או כמה שיותר, או בכלל צריך לקבוע את זה לפי לוגיקה מסויימת(למשל- כאלה שאמורות להחזיר ערך יחיד שלא נשלף ישירות מטבלה, אלא מחושב לפי נוסחא מסויימת לדוגמא)?

[NJorl]

יכול להיות שאני אמצא דרך לעשות INNER JOIN. הבעיה היא שזה קצת יותר מסובך מ- 3 טבלאות. בכל מקרה, השאלה היא האם אני יכול להשתמש בPROCEDURES בתוך SELECT. הבנתי שהן יותר יעילות מפונקציות.

אם בכל זאת אחליט להעביר הכל לפונקציות - אילו פרוצדורות כדאי לי להמיר? רק אלה שאני משתמש בהן בתוך WHERE, או כמה שיותר, או בכלל צריך לקבוע את זה לפי לוגיקה מסויימת(למשל- כאלה שאמורות להחזיר ערך יחיד שלא נשלף ישירות מטבלה, אלא מחושב לפי נוסחא מסויימת לדוגמא)?

הפרוצדורות הן בד"כ הממשק שלך לקוד החיצוני , הקוד החיצוני מדבר רק עם פרוצדורות, ועד כמה שידוע לי אתה לא קורא לפונקציות SQL מבחוץ, כלומר אתה כן יכול אם אתה כותב SQL בקוד שזה אתה כבר יודע מאוד לא מומלץ, בקוד כתוב היטב אתה תעדיף להשתמש ב- DataAdapters ואלו מפעילים פרוצדורות.

פרוצדורות יכולות להחזיר רשומות או סקאלרים

פונקציות שימושיות לניהול הפנימי בתוך ה- sql server כמו בדוגמא שדיברנו עליה כאן , אותן אפשר לשבץ בתוך select או where של פרוצדורה.

בתוך פונקציות אסורות הפקודות שמשנות נתונים delete, insert , update

פונקציות חייבות להחזיר איזשהו ערך ורק סקאלרי

[Ghosthunter]

את הבדיקה הזו אני מעוניין לבצע גם מחוץ לפרוצדורות.

האם עלי ליצור גם פרוצדורה וגם פונקציה שיעשו אותו דבר? אולי יש באפשרותי לקרוא לפרוצדורה מתוך פונקציה וכך לחסוך קצת קוד?

אני לא חושב שכדאי לעשות את זה מכיוון שזה סתם מסבך את המערכת.

אם כך, חזרנו לשאלה הראשונה שלי.

[NJorl]

יש הבדלים בין פונקציות לפרוצדורות ושווה לקרוא על זה עוד.

בפונקציות אתה לא יכול לכתוב משפטי SQL שמשנים נתונים , insert, delete update

פונקציות יכולות להחזיר רק סקאלרים

פונקציות נועדו לשימוש בדיוק למצבים בהן נתקלת, כלומר שילוב ביטוי לוגי בתוך where של שאילתה\פרוצדורה

פונקציות בד"כ ישמשו בשימוש פנימי בתוך ה- SQL Server כלומר פרוצדורות או שאילתות יקראו להן , אבל לא מתוך קוד חיצוני

פונקציות שימושיות גם כאשר אתה רוצה להגדיר ברירות מחדל לשדה בטבלה

פרוצדורות משמשות כשכבה המתווכת בין הקוד החיצוני ל-Database שלך, אף פעם אל תיגש לטבלאות בין אם לשליפה ובין אם לצורך עדכון ישירות מקוד, בדיוק בשביל זה נועדו הפרוצדורות.

פרוצדורות יכולות להחזיר רשומות

כאשר אתה צריך לחשוף פונקציה אל הקוד החיצוני , פשוט תכתוב פרוצדורה שעוטפת את הפונקציה.

[Ghosthunter]

אוקיי. אני חושב שהבנתי עכשיו. תודה רבה לך!

לאחר בדיקה קלה - לא הייתי צריך ליצור פונקציה בסוף. הייתי צריך לרשום את השם המלא של הפרוצדורה(dbo.CanDoSomething, ולא CanDoSomething בלבד), ובלי EXEC בצורה הבאה:

select *
from table
where dbo.myProcedure(table.id) = 1