CODEWITHSUNDEEP
c#sql
Leon
Leon

Reputation: 23

getting sql statement behind view

I have a c# application (2008) that gets data from sql server (2005). I have a view in sql server that prepares data for display, something like this (simplified):

select Places.Name as [Location], Parts.Name as [Part Name]
from  Places inner join Parts 
on Places.Id=Parts.Location

I have to filter this with "where" statement that is built in code and is like:

where (Places.Id=1 or Places.Id=15) and 
      (Parts.Id=56 or Parts.Id=8 or Parts.Id=32)

I can of course keep the basic select statement in my code, but i likw to have things defined only in one place :) and the question is if there is any way to get the select statement behind the view in sql server? Or to get the contents of stored procedure? Thanks a lot!

Upvotes: 2

Views: 401

Answers (3)

jb_
jb_

Reputation: 958

If you want a stored procedure to execute your query (and combining your basic query string, with your where clause), you can accomplish this by using the following code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string selectCommand = "EXEC sp_YourStoredProcedure @whereClause";

    SqlCommand command = new SqlCommand(selectCommand, connection);
    command.Parameters.Add("@whereClause", System.Data.SqlDbType.NVarChar);
    command.Parameters["@whereClause"] = whereClause;
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.NextResult())
        {
            string location = reader.GetString(0);
            string partName = reader.GetString(1);

            // do something
        }
    }

    connection.Close();
}

Edit: Example of dynamic stored procedure:

CREATE PROCEDURE sp_YourStoredProcedure
(
    @whereClause NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX)

    SET @sql = N'
    select Places.Name as [Location], Parts.Name as [Part Name] 
    from  Places inner join Parts  
    on Places.Id=Parts.Location '
    + @whereClause

     EXEC sp_executesql @sql
END

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

Using the information schema views as jani suggested is one option.

Another is using the sp_helptext system stored procedure. sp_helptext YourView or sp_helptext YourStoredProcedure gets you the entire object definition.

You can find more information about the at sp_helptext system stored procedure here.

Upvotes: 0

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

Take a look at Information Schema View, you may find your solution.

Upvotes: 1

Related Questions