Retreiving Data as XML from MS SQL server
-
<hello, I am trying to develop a simple application to retrieve data from MS SQL server using XML Templates. The application consists of a main HTML page with a form. The form has 3 text box to filter data (ID, name and title). When the user pushes the "submit" button a XML template is loaded and the 3 text box values are passed as parameters: For Example: http://localhost/nwind/template/EmployeesList.xml?EmployeeID=1&Name=Nancy&Title=Sales+Representative&Submit=Go The XML template is this: SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE EmployeeID = @EmployeeID AND FirstName = @Name AND Title = @Title FOR XML AUTO As you can see, the template use the 3 params to filter data. This runs ok if the user sets well the 3 values in the text boxes but if the user leaves a text box blank it does not. What i want to do is a filter: if the user only sets text in a text box the application has to list every employees with this text box value. Could you help me? How can i do this? thank you very much!
-
<hello, I am trying to develop a simple application to retrieve data from MS SQL server using XML Templates. The application consists of a main HTML page with a form. The form has 3 text box to filter data (ID, name and title). When the user pushes the "submit" button a XML template is loaded and the 3 text box values are passed as parameters: For Example: http://localhost/nwind/template/EmployeesList.xml?EmployeeID=1&Name=Nancy&Title=Sales+Representative&Submit=Go The XML template is this: SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE EmployeeID = @EmployeeID AND FirstName = @Name AND Title = @Title FOR XML AUTO As you can see, the template use the 3 params to filter data. This runs ok if the user sets well the 3 values in the text boxes but if the user leaves a text box blank it does not. What i want to do is a filter: if the user only sets text in a text box the application has to list every employees with this text box value. Could you help me? How can i do this? thank you very much!
ok, I have solved my problem. I have changed the XML template. Now, the XML template is this: exec StoreProcedure @EmployeeID, @Name, @Title As you can see, it executes a store procedure and pass it the 3 params. Then, in the store procedure the params are checked and in function of their values a query is executed. Problem: I dont know much about SQL and it must be a query for each one of the possible combinations. I think this is very inefficient. The store procedure is this: CREATE PROCEDURE StoreProcedure @EmployeeID int, @Name nvarchar(30), @Title nvarchar(60) AS IF (@EmployeeID < 1 ) AND (@Name = '') AND (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@Name = '') AND (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE EmployeeID = @EmployeeID ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@EmployeeID < 1 ) AND (@Name = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE Title = @Title ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@EmployeeID < 1 ) AND (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE FirstName=@Name ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@EmployeeID < 1 ) BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE FirstName=@Name AND Title = @Title ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@Name = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE Title = @Title AND EmployeeID = @EmployeeID ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE FirstName = @Name AND EmployeeID = @EmployeeID ORDER BY EmployeeID FOR XML AUTO GOTO FIN END FIN: GO Is there another way to do this more efficiently? Thank you very much!
-
ok, I have solved my problem. I have changed the XML template. Now, the XML template is this: exec StoreProcedure @EmployeeID, @Name, @Title As you can see, it executes a store procedure and pass it the 3 params. Then, in the store procedure the params are checked and in function of their values a query is executed. Problem: I dont know much about SQL and it must be a query for each one of the possible combinations. I think this is very inefficient. The store procedure is this: CREATE PROCEDURE StoreProcedure @EmployeeID int, @Name nvarchar(30), @Title nvarchar(60) AS IF (@EmployeeID < 1 ) AND (@Name = '') AND (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@Name = '') AND (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE EmployeeID = @EmployeeID ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@EmployeeID < 1 ) AND (@Name = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE Title = @Title ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@EmployeeID < 1 ) AND (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE FirstName=@Name ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@EmployeeID < 1 ) BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE FirstName=@Name AND Title = @Title ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@Name = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE Title = @Title AND EmployeeID = @EmployeeID ORDER BY EmployeeID FOR XML AUTO GOTO FIN END IF (@Title = '') BEGIN SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE FirstName = @Name AND EmployeeID = @EmployeeID ORDER BY EmployeeID FOR XML AUTO GOTO FIN END FIN: GO Is there another way to do this more efficiently? Thank you very much!
It looks like your stored procedure can be simplified to one select statement:
SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE (@EmployeeID < 1 or EmpolyeeID = @EmployeeID) and (@Name = '' or FirstName = @Name) and (@Title = '' or Title = @Title) ORDER BY EmployeeID FOR XML AUTO[My articles and software tools