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.
Author |
Topic |
cma2008
Starting Member
1 Post |
Posted - 2008-04-18 : 07:14:31
|
Hello People,I am Seema a first user in this forum. I am new to SQl and also .net. I am having a timesheet database where in I need to export those datas to Excel using stored procedure.If I use a DBMail it works fine, but the issue is I am not able to pass parameters in query. For example the following code works fine,DECLARE @tableHTML NVARCHAR(MAX) ;SET @tableHTML = N'<H1>GSR Time Entry</H1>' + N'<table border="1">' + N'<tr><th>Employee ID</th><th>Employee Name</th>' + N'<th>Age</th>' + CAST ( ( SELECT td = E.Employeeid, '', td = E.Name, '', td = E.Age, '' FROM Employee as E FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;EXEC msdb.dbo.sp_send_dbmail @recipients='trial@yahoo.com', @profile_name = 'seema', @subject = 'Time Sheet', @body = @tableHTML, @body_format = 'HTML' This code works fine, but in the same query when I try to send parameters say 'dept' it doesnot recognise the database at all.I need a stored procedure which will export SQL data to an excel and I should be able to pass parameters and get data, i.e., I should be able to use the following query and the result should be exported in Excel.ALTER procedure [dbo].[sp_email](@dept varchar (50),@exp varchar (50))asbeginselect EmployeeID,Name,Age from employee where DepartmentName= @dept andExperience = @exp Please help and any help and reply will be very much appreciatedRegards,Seemaseema |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-04-20 : 00:38:54
|
Have you just tried putting the parameters into the query that you already have?ALTER procedure [dbo].[sp_email](@dept varchar (50),@exp varchar (50))asDECLARE @tableHTML NVARCHAR(MAX) ;SET @tableHTML =N'<H1>GSR Time Entry</H1>' +N'<table border="1">' +N'<tr><th>Employee ID</th><th>Employee Name</th>' +N'<th>Age</th>' +CAST ( ( SELECT td = E.Employeeid, '',td = E.Name, '',td = E.Age, '' FROM Employee as EWHERE DepartmentName = @dept AND Experience = @exp FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +N'</table>' ;EXEC msdb.dbo.sp_send_dbmail @recipients='trial@yahoo.com',@profile_name = 'seema',@subject = 'Time Sheet',@body = @tableHTML,@body_format = 'HTML' |
|
|
|
|
|
|
|