Please Help me in this stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please Help me in this stored procedure

Author

Topic

pericherla
Starting Member

3 Posts

Posted - 2007-11-06 : 14:47:22

Below is the code which need to b changed to stored proc but with out using dynamic sql...........
I am struggling from 2 days
I appreciate if any one can help Thanks in Advance
strSQL = "SELECT M.ID, m.BUDGET_CAMPUSID, " & _
" M.VENDOR_ID AS VENDOR_CODE, M. VENDORNAME AS VENDOR_NAME, " & _
" VENDOR_ADDRESS = " & _
" CASE " & _
" WHen M.VENDORADDRESS2 IS NULL THEN M.VENDORADDRESS1 " & _
" ELSE " & _
" M.VENDORADDRESS1 + ' ' + M.VENDORADDRESS2 " & _
" END, " & _
" isSubmitted = " & _
" case " & _
" when m.submit_Date is null then ' N' " & _
" else 'Y' " & _
" End, " & _
" (M.VENDORCITY + ', ' + M.VENDORSTATE + ' ' + M.VENDORZIP) as VENDOR_address2, " & _
"EXCEPTION_DESC, " & _
"SUBMIT_DATE, " & _
"p.purpose as exception_Flag, " & _
"status = " & _
"case status " & _
"when 'W' then 'In process' " & _
"when 'A' then 'Approved' " & _
"when 'C' then 'Cancelled' " & _
"when 'D' then 'Disapproved' " & _
"End, " & _
"checkNo = CASE WHEN m.checkNum IS NULL THEN ' ' ELSE 'Chk Date: ' + CONVERT(CHAR(10), actCheckDate, 101) " & _
" + ' Chk No: ' + m.checkNum END " & _
"FROM RFPMASTER M, PURPOSE P WHERE M.EXCEPTION_FLAG = P.ID "
If id <> "" Then
If IsNumeric(id) Then
strSQL = strSQL & " AND M.ID = '" & id & "' "
End If
End If
If strVendorCode <> "" Then
strSQL = strSQL & " AND M.VENDOR_ID LIKE '%" & strVendorCode & "%' "
End If
If strCampus <> "-1" Then
strSQL = strSQL & " AND BUDGET_CAMPUSID = '" & strCampus & "' "
End If
If strPurpose <> "-1" Then
strSQL = strSQL & " AND EXCEPTION_FLAG = '" & strPurpose & "' "
End If
If strStatus <> "-1" Then
If strStatus = "A" Or strStatus = "W" Then
strSQL = strSQL & " AND STATUS = '" & strStatus & "' "
Else
strSQL = strSQL & " AND STATUS != 'A' AND STATUS != 'W' "
End If
End If
If strTime <> "-1" Then
If strTime = "1" Then
strSQL = strSQL & " and datediff(month, m.input_Date, getdate()) < 1 "
ElseIf strTime = "2" Then
strSQL = strSQL & " and datediff(month, m.input_Date, getdate()) < 3 "
ElseIf strTime = "3" Then
strSQL = strSQL & " and datediff(month, m.input_Date, getdate()) < 6 "
ElseIf strTime = "4" Then
strSQL = strSQL & " and datediff(month, m.input_Date, getdate()) < 12 "
ElseIf strTime = "5" Then
strSQL = strSQL & " and datediff(year, m.input_Date, getdate()) > 1 "
End If
End If
Dim roleSQl As String = ""
If Not isAdminAccouting And id = "" Then
If myRole = "A" Then
roleSQl = " AND BUDGET_CAMPUSID IN (SELECT DISTINCT " & _
" SUBSTRING(P.LOCATIONID, 1,3) FROM COMMON.DBO.LOCATIONS P, " & _
" employee.dbo.EMPLOYEES hr WHERE " & _
" hr.ssn = p.ASSOCIATESUPERSSN and hr.EMPLOYEEID = '" & eid & "')"
ElseIf myRole = "E" Then
roleSQl = " AND BUDGET_CAMPUSID IN (SELECT DISTINCT " & _
" SUBSTRING(P.LOCATIONID, 1,3) FROM COMMON.DBO.LOCATIONS P, " & _
" employee.dbo.EMPLOYEES hr WHERE " & _
" hr.ssn = p.EXECUTIVEDIRECTORSSN and hr.EMPLOYEEID = '" & eid & "')"
ElseIf myRole = "B" Then
roleSQl = " AND BUDGET_CAMPUSID IN (SELECT DISTINCT " & _
" SUBSTRING(P.LOCATIONID, 1,3) FROM COMMON.DBO.LOCATIONS P, " & _
" employee.dbo.EMPLOYEES hr WHERE " & _
" hr.ssn = p.BudgetManagerSsn and hr.EMPLOYEEID = '" & eid & "')"
Else
roleSQl = " AND BUDGET_CAMPUSID IN (SELECT ORG_ID FROM COMMON.DBO.CS_CAMPUS_ACCESS WHERE EMP_ID = '" & eid & "')"
End If
strSQL = strSQL & roleSQl
End If
Dim sortSql As String = ""
If strSort = "1" Then
sortSql = " order by M.id "
ElseIf strSort = "2" Then
sortSql = " order by M. VENDORNAME "
ElseIf strSort = "3" Then
sortSql = " order by m.input_Date"
ElseIf strSort = "4" Then
sortSql = " order by m.BUDGET_CAMPUSID "
End If
strSQL = strSQL & sortSql
Return SQLHelper.ExecuteDataSet(strSQL)
Catch ex As Exception
Throw ex
End Try

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-06 : 14:53:03

Use exec(string)
General logic will be the same.

I hope you did not expect anyone to rewrite your hundred lines of code :)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-06 : 19:53:05

Dynamic sql is a very dangerous thing. I would definatly code out the stored procedure. Here's a example of how to do dynameic sql a safe way w/o passing strings, only variables


Create Proc Sproc_MyProcedure
@ID int,
@VendorCode Varchar(30)
as         
SELECT M.ID
, m.BUDGET_CAMPUSID
, M.VENDOR_ID AS VENDOR_CODE
, M. VENDORNAME AS VENDOR_NAME
, (
	CASE  
	WHen M.VENDORADDRESS2 IS NULL THEN M.VENDORADDRESS1  
	ELSE M.VENDORADDRESS1 + ' ' + M.VENDORADDRESS2  
	END 
  ) as  VENDOR_ADDRESS 
,(
	case  
	when m.submit_Date is null then 'N'  
	else 'Y'  
	End
 ) as IsSubmited
,M.VENDORCITY + ', ' + M.VENDORSTATE + ' ' + M.VENDORZIP as VENDOR_address2
,EXCEPTION_DESC
,SUBMIT_DATE
,z.purpose as exception_Flag
,(
	Case status  
	when 'W' then 'In process'  
	when 'A' then 'Approved'  
	when 'C' then 'Cancelled'  
	when 'D' then 'Disapproved'  
	End as Status
 )
,(
	CASE WHEN m.checkNum IS NULL THEN ' ' 
	ELSE 'Chk Date: ' + CONVERT(CHAR(10), actCheckDate, 101)  + ' Chk No: ' + m.checkNum 
	END  
 ) as CheckDate
FROM 
	RFPMASTER M
Inner Join
	PURPOSE P 
on		 M.EXCEPTION_FLAG = P.ID 
Where
		m.ID = Coalesce(@ID,m.ID)	
and		M.VENDOR_ID like '%' + coalesce(@VendorCode,M.VENDOR_ID)+ '%'

Just create a variable for each thing you need to pass and code it the same way I did in the where clause above.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-07 : 01:28:55

http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources