CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-services
SJTaylor
SJTaylor

Reputation: 23

TSQL query to end user

I have written a parameterized transact SQL query for a member of our finance department and several times during the month I run it and copy the raw output with headers into excel for him. Now that department is being regionalised and I've got several finance departments all wanting the same thing.

I know that SSRS will be deployed eventually but our infrastructure team are building a new environment and don't want any new installations in the 'old world' for the moment.

I just need a way to give select individuals access to run that parameterized query against the database. I had thought about turning the query into a view and creating logins for their network accounts with access only to that view but I don't think you can use parameters with views. I wondered if there is a simple interface that can allow them to enter parameters against a stored query or view without using SSRS. It seems so simple but I'm not having much luck finding out.

Sorry if this is a stupid question but I've just moved from server admin to a DBA role and I've only just scratched the surface!

Upvotes: 0

Views: 52

Answers (2)

BeaglesEnd
BeaglesEnd

Reputation: 421

You can create a UDF which will return a table based on the parameters. For example:

CREATE FUNCTION [dbo].[fnt_myfunction]( @id INT)
RETURNS TABLE
AS
RETURN (
        SELECT *
        FROM myTable
        WHERE id = @id
        );

DECLARE @id INT = 1;  

SELECT * FROM [dbo].[fnt_myfunction](@id);

Hope that helps.

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Create a view and called that in SP with Parameter:-

Sample would be

Create View [dbo].[vw_sampleView]
AS
BEGIN
 SELECT * FROM tblSample
END


CREATE PROC [dbo].[proc_GetData]
@id int
AS    
BEGIN
 SELECT * FROM vw_sampleView where id= @id
END

Then this SP retunred filtered data. Grant the permission to execute this SP to different users.

GRANT EXECUTE ON [dbo].[proc_GetData] TO [user_logins]

Upvotes: 1

Related Questions