SQL Server 2008 :: Search For A Stored Procedure

Jun 25, 2015

I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?

View 1 Replies


ADVERTISEMENT

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

Search For A Stored Procedure On Entire Server?

Nov 6, 2012

sp_MSforeachdb 'Select ''?'' as Dbname, SPECIFIC_SCHEMA, ROUTINE_NAME
From ?.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME like ''%YourSPName%'''

View 1 Replies View Related

More Tables Do Search,please Help Me,sql Server Stored Procedure Problem

Oct 12, 2006

i have more tables

exmaple:

table1(id,title,content,date)

table2(did,type,title,text1,text2,requestdate)

table3(subid,value1,value2,value3,value4)

.......................15 tables left,some exmaple

now i want use stored procedure do search for asp.net

asp.net send search keyword to sql server,sql server get keywords execute search and return results to web application(asp.net)

i don't konw how what write t-sql,please friends help me.

because table colums is different,so need write t-sql put the temp DATA TO Temp DataTable,last return Temp DataTable to asp.net,asp.net execute split pager display data,eventable colums is different,so on asp.net links is different,need t-sql programming,hope friends can help me complete this effect(i need code,C#/VB.NET +T-SQL).

i at ago like this do.

<%@ Page Language="C#"  ValidateRequest="false"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    private void page_init()
    {
        if (!IsSecure())
        {
            Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
        }
    }


    private void page_load(object sender, EventArgs e)
    {
        string CurrentPageValue = "";
        string Qu = "";
        Double PageValue = 0;
        CurrentPageValue = Request.QueryString["PageN"];
        Qu = Request.QueryString["QuKey"];
        Qu = ReplaceStr(Qu);
        if (Qu != null)
        {
            if (IsNumber(CurrentPageValue))
            {
                PageValue = Convert.ToDouble(CurrentPageValue.Trim());
                Init_Data(PageValue,Qu);
            }
            else
            {
                PageValue = 1;
                Init_Data(PageValue,Qu);
            }
        }
        else
        {
            ExecuteError();
        }
    }


    private void Init_Data(Double CurrentPage,string QuKey)
    {
        try
        {
            string ConStr = GetConnectionString();
            string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
            string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
            string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
            string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%'  order by date desc";
            //expmale for test,i have 17 tables,so write some table do test
            System.Data.DataTable ResultsTable = new System.Data.DataTable();
            ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
            ResultsTable.Columns.Add("title", System.Type.GetType("String"));
            ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
            ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
            ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
            ResultsTable.Columns[0].Unique = true;
            ResultsTable.Columns[0].AutoIncrement = true;
            ResultsTable.Columns[0].AutoIncrementStep = 1;
            ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
            System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
            System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
            System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
            System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
            System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
            System.Data.DataSet Ds = new System.Data.DataSet();
            SearchConnect.Open();
            ada1.Fill(Ds, "table1");
            ada2.Fill(Ds, "table2");
            ada3.Fill(Ds, "table3");
            ada4.Fill(Ds, "table4");
            string ReTitle = "";
            string ReUrl = "";
            string ReIntro = "";
            DateTime ReDate = System.DateTime.Now;
            if (Ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
                {
                    System.Data.DataRow TempRow;
                    TempRow = ResultsTable.NewRow();
                    ReTitle = Ds.Tables[0].Rows[1].ToString();
                    //url need check before count get detail
                    //for test temp defined
                    ReUrl = "url1";
                    ReIntro = Ds.Tables[0].Rows[1].ToString();
                    ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
                    TempRow[1] = ReTitle;
                    TempRow[2] = ReUrl;
                    TempRow[3] = ReIntro;
                    TempRow[4] = ReDate;
                    ResultsTable.Rows.Add(TempRow);
                }
            }
            if (Ds.Tables[1].Rows.Count > 0)
            {
                for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
                {
                    System.Data.DataRow TempRow;
                    TempRow = ResultsTable.NewRow();
                    ReTitle = Ds.Tables[1].Rows[1].ToString();
                    //url need check before count get detail
                    //for test temp defined
                    ReUrl = "url2";
                    ReIntro = Ds.Tables[1].Rows[1].ToString();
                    ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
                    TempRow[1] = ReTitle;
                    TempRow[2] = ReUrl;
                    TempRow[3] = ReIntro;
                    TempRow[4] = ReDate;
                    ResultsTable.Rows.Add(TempRow);
                }
            }
            //........loop to table end
            //................................
            Ds.Dispose();
            ada1.Dispose();
            ada2.Dispose();
            ada3.Dispose();
            ada4.Dispose();
            SearchConnect.Dispose();
            if (SearchResults.Rows.Count > 1)
            {
                DisplayData(CurrentPage, QuKey, SearchResults);
            }
            else
            {
                SearchResults.Dispose();
                ExecuteError();
            }      
        }
        catch
        {
            ExecuteError();
        }
    }


    private void DisplayData(Double PageNumber, string Keywords, System.Data.DataTable CurrentData)
    {
        Double TotalPage = 0;
        Double TotalRow = 0;
        Double TempPage1 = 0;
        decimal TempPage2 = 0;
        int StartValue = 0;
        int EndValue = 0;
        int PageSize = 30;
        TotalRow = CurrentData.Rows.Count;
        TempPage1 = TotalRow/PageSize;
        TempPage2 = Convert.ToDecimal(TotalRow / PageSize);
        if (Convert.ToDouble(TempPage2 - TempPage1) > 0)
        {
            TotalPage = TotalRow / PageSize + 1;
        }
        else
        {
            TotalPage = TotalRow / PageSize;
        }
        if (TotalPage < 1) { TotalPage = 1; }
        if (PageNumber > TotalPage) { PageNumber = TotalPage; }
        if (PageNumber == TotalPage)
        {
            EndValue = TotalRow;
        }
        else
        {
            EndValue = PageNumber * PageSize;
        }
        StartValue = PageNumber * PageSize - PageNumber;
        for (int j = StartValue; j < EndValue; j++)
        {
            System.Web.UI.WebControls.TableRow Tr = new TableRow();
            System.Web.UI.WebControls.TableCell Td1 = new TableCell();
            System.Web.UI.WebControls.TableCell Td2 = new TableCell();
            System.Web.UI.WebControls.TableCell Td3 = new TableCell();
            Td1.Controls.Add(new LiteralControl(CurrentData.Rows[j][1].ToString()));
            Td2.Controls.Add(new LiteralControl(CurrentData.Rows[j][2].ToString()));
            Td3.Controls.Add(new LiteralControl(CurrentData.Rows[j][3].ToString()));
            Tr.Cells.Add(Td1);
            Tr.Cells.Add(Td2);
            Tr.Cells.Add(Td3);
            SearchResults.Rows.Add(Tr);
        }
        System.Web.UI.WebControls.TableRow StateTr = new TableRow();
        System.Web.UI.WebControls.TableCell StateTd = new TableCell();
        StateTd.ColumnSpan = 3;
        if (PageNumber - 1 > 0)
        {
            StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber - 1) + "&qu="+Keywords+">prev</a>"));
        }
        if (PageNumber !=TotalPage)
        {
            StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber + 1) + "&qu="+Keywords+">Next</a>"));
        }
        StateTr.Cells.Add(StateTd);
        SearchResults.Rows.Add(StateTr);
        CurrentData.Dispose();
        SearchResults.Dispose();
        //rows count > 200,runtime at 1.6 seconds left,
        //rows count > 1000,runtime at 8 seconds left,
        //rows count >10000,runtime at 12 seconds left
        //execute speed is very bad,so i want use sql stored procedure search,but i don't write t-sql,hope friends hope me,thanks
    }

    private void ExecuteError()
    {
        SearchResults.Controls.Add(new LiteralControl("we're sorry,we unable find even contain your key data,please try other keywords.suppot to ...."));
        SearchResults.Dispose();  
    }
   
    private bool IsSecure()
    {
        if (!Page.Request.IsSecureConnection)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    private bool IsNumber(string Restr)
    {
        try
        {
            if (Restr != null)
            {
                string TempStr = Restr.Trim();
                if (TempStr != "")
                {
                    System.Text.RegularExpressions.Regex MyTry = new Regex("@[0-9]*$");
                    if (MyTry.IsMatch(TempStr))
                    {
                        Double valueR;
                        Double = Convert.ToDouble(TempStr);
                        if (valueR >= 1 && valueR <= 10000)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        return false;
                    }

                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        catch
        {
            return false;
        }
    }

    private string GetConnectionString()
    {
        try
        {
            string ConnectionString = "datasource=......,initial catalog=databasename,trusted_connection=yes;integrate security info=true;";
            return ConnectionString;
        }
        catch
        {
            return "!";
        }
    }

    private string ReplaceStr(string k)
    {
        try
        {
            if (k != null)
            {
                string r = k.Trim();
                if (r != "")
                {
                    r = r.Replace("~", "");
                    r = r.Replace("!", "");
                    r = r.Replace("@", "");
                    r = r.Replace("#", "");
                    r = r.Replace("$", "");
                    r = r.Replace("%", "");
                    r = r.Replace("^", "");
                    r = r.Replace("&", "");
                    r = r.Replace("*", "");
                    r = r.Replace("(", "");
                    r = r.Replace(")", "");
                    r = r.Replace("\", "");
                    r = r.Replace("|", "");
                    r = r.Replace("[", "");
                    r = r.Replace("]", "");
                    r = r.Replace("{", "");
                    r = r.Replace("}", "");
                    r = r.Replace(":", "");
                    r = r.Replace(";", "");
                    r = r.Replace("'", "");
                    r = r.Replace("", "");
                    r = r.Replace("<", "");
                    r = r.Replace(">", "");
                    r = r.Replace("!", "");
                    r = r.Replace(",", "");
                    r = r.Replace(".", "");
                    r = r.Replace("?", "");
                    r = r.Replace("/", "");
                    r = r.Trim();
                    return r; 
                }
            }
            else
            {
                return "";
            }
        }
        catch
        {
            return "";
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Do Search SQL Server Page</title>
</head>
<body>
    <asp:Table runat="server" ID="SearchResults">
    </asp:Table>
</body>
</html>

 

 

 

 

 

MyTest:

 

SQL SERVER 2005+WINDOWS SERVER 2003+.NET 2.0

View 3 Replies View Related

SQL Server 2008 :: Create A Trigger With A Stored Procedure?

Jun 5, 2012

I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :

Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1

Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.

Here is the code for the stored procedure :

CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END

View 5 Replies View Related

SQL Server 2008 :: Using Execute As And Encryption In Stored Procedure

Oct 7, 2013

I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.

The below query is getting errors:

Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table

View 3 Replies View Related

SQL Server 2008 :: Allow User Only Alter Stored Procedure

Aug 10, 2015

I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.

I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.

View 1 Replies View Related

SQL Server 2008 :: Need To Recompile Stored Procedure After A Table Alter?

Feb 5, 2015

Version 2008 R2

The stored procedure has the dependency on the table that was altered.

View 4 Replies View Related

SQL Server 2008 :: Skip Code If Certain Condition Not Met In Stored Procedure

Mar 18, 2015

I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?

View 5 Replies View Related

SQL Server 2008 :: Stored Procedure Text String Replace

Apr 16, 2015

We have a legacy database that have hundreds of stored procedures.

The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.

I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.

I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?

View 2 Replies View Related

SQL Server 2008 :: Query Execution Plan Of Stored Procedure

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 8 Replies View Related

SQL Server 2008 :: Replication Of Stored Procedure Execution Fails

Jul 29, 2015

I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:

IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name

[Code] ....

I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:

ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)

How can I change my code to workaround this? Perhaps some explicit transactions?

View 6 Replies View Related

SQL Server 2008 :: Calling Stored Procedure Into Report Builder

Oct 23, 2015

I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.

View 3 Replies View Related

SQL Server 2008 :: Find All Items That Call A Stored Procedure

Nov 2, 2015

We have a process that uses a stored procedure: sp_MysteryProcedure....

The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.

One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).

So my question is multi-part.....

1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?

View 9 Replies View Related

SQL Server 2008 :: Backup Of Distribution Database Hangs On Stored Procedure

Nov 29, 2014

Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.

Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.

This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.

I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.

As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.

View 1 Replies View Related

SQL Server 2008 :: Copy Multiple CSV Files Into Database Using Stored Procedure?

Feb 9, 2015

I need to import multiple csv files and load into table and everytime new database has to be created .

I was able to create new databases using stored proc

How do i do a bulk insert for all the files at once to insert into tables .

i want to load all the files at once .

View 6 Replies View Related

SQL Server 2008 :: Running Batch File To Execute A Stored Procedure

May 11, 2015

I have create a batch file to execute a stored proc to import data.

When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?

View 9 Replies View Related

SQL Server 2008 :: Find List Of Stored Procedure Used By Crystal Reports

Jul 1, 2015

We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?

View 4 Replies View Related

SQL Server 2008 :: Trigger To Show Which Stored Procedure Has Updated A Table

Jul 9, 2015

I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table

At present I have:

CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)

[Code] ...

However this doesn't appear to bring back the procedure that triggered the update...

The value the trigger should update on is document_status = 0

DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?

Jul 22, 2015

isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.

View 3 Replies View Related

SQL Server 2008 :: Stored Procedure Script Output To Text File

Apr 29, 2009

I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.

View 9 Replies View Related

SQL Server 2008 :: Granting Explicit View Definition Permissions On Stored Procedure To DBO?

Mar 6, 2013

The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.

While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.

When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.

Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?

View 9 Replies View Related

SQL Server 2008 :: Stored Procedure Returning Different Datasets Based On Input Variable

Sep 15, 2015

I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:

CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial

[Code] ....

As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.

So what I'm looking for is basically 2-fold.

View 5 Replies View Related

SQL Stored Procedure Issue - Search Stored Procedure

May 18, 2007

This is the Stored Procedure below -> 
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/2/2007 4:52:19 PM ******/
 
CREATE  PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
 
IF (@i_WildCardFlag=0)BEGIN
 SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch  INNER JOIN   BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND  ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
 if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
 if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch  INNER JOIN  BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND  ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
 if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
 if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
 
 
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
 
Please help me regarding the same.
 
Thanks in advance.
 
Sandeep Kumar
 

View 2 Replies View Related

Stored Procedure For Search

Dec 14, 2007

Hi..I am working With Asp.net using Vb for a Music Project.i have the requirment for serach songs according to catagory wise(Singer,Actor,Music Director, etc)
i have code like this...
 If Not Page.IsPostBack Then            searchword.Text = Request.QueryString("SearchWord")            Response.Write(Request.QueryString("SearchWord"))            Response.Write(Request.QueryString("Language"))            Response.Write(Request.QueryString("SelectedCategory"))            'Response.Write(Request.QueryString("Query"))
            Dim str As String = "select * from Music_SongDetails where Music_Clip_Id>0 and Music_Clip_Lang='" & Request.QueryString("Language") & "'"
            If Request.QueryString("SelectedCategory") = "Song" Then                str = str & " and Music_Clip_Name like '%" & Request.QueryString("SearchWord") & "%'"            ElseIf Request.QueryString("SelectedCategory") = "Movie" Then                str = str & " and Music_Folder_Name='" & Request.QueryString("SearchWord") & "'"            ElseIf Request.QueryString("SelectedCategory") = "Actor" Then                str = str & " and Music_Clip_Actor='" & Request.QueryString("SearchWord") & "'"            ElseIf Request.QueryString("SelectedCategory") = "Actress" Then                str = str & " and Music_Clip_Actress='" & Request.QueryString("SearchWord") & "'"            ElseIf Request.QueryString("SelectedCategory") = "Music Director" Then                str = str & " and Music_Clip_MusicDir='" & Request.QueryString("SearchWord") & "'"            ElseIf Request.QueryString("SelectedCategory") = "Singer" Then                str = str & " and Music_Clip_Singer='" & Request.QueryString("SearchWord") & "'"            ElseIf Request.QueryString("SelectedCategory") = "All" Then                str = str            End If...........
I need to write this code using Store Procedure....
 Kindly Help me out
Thanks in Advance

View 3 Replies View Related

Stored Procedure For Search

Mar 29, 2008

hi iam working with search for the first time,in the GUI i have 3 fields Audit Name,Year,Audit ID.After enetering any or all these details and pressing submit i must show the gridview with complete details.
I have problem with the procedure for searching depending on the details given,here is the procedure:
Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Reg.Login_Uname,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
RegistrationDetails Reg,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Reg.UID_PK =Aud.Audit_Engineer and
Ad_ID_PK in (select Ad_ID_PK from Pcra_Audits) and
year(Audit_Created_On)=year(@YrofAudit)
order by Audit_Created_On DESC
iam getting the data when the user enters year but i want the procedure where i can check for the three fields(Audit Name,Year,Audit ID) which user is entering.If he enters only one field it must check which field is enetered and must get the data.if more than one field is entered then all the conditions must be checked and must get the details.please help me..........
Its very urgent..Plz...

View 2 Replies View Related

Search Stored Procedure

Aug 27, 2002

I am an inexperienced SQL programmer and need to write a SP which will be used to search a Call table within a Call Logging System used to log support calls for my company. The search criteria are fields like Call Reference No, Logged By, Call Status etc

The problem I have is that individual or a combination of these criteria may be used to search on -can anyone advise how I can write a SP which will take account of the possible different combinations of parameters which may be passed to the Stored Procedure

i.e. if 2 fields are populated during the search and 4 are empty

Thanks,
Stephen

View 1 Replies View Related

Stored Procedure For Search

Nov 27, 2007

Hi to All,
I am new to Prpgramming, I need to create a Stored Procedure for my requirement here is my requirement,I have two tables from those I need to get data. Table_One consists UserID,Name,Address,ContactInfo,EmailID, and Table_two consists UserID,CitizenShip,HieghestEducation,ExpectedJob
But I need get data search report Name,EmailID,HiehestEducation,ExpectedJob. User should able to wile card search. Pls help me in this regards.
Thanks in Advance..

View 1 Replies View Related

How To Search For A Stored Procedure?

Oct 9, 2006

Hi,

Could anybody please tell me how I can search for a stored procedure in SQL Server 2005? I know the name of the stored procedure and I want to find in which database that stored proc is located/stored and I want to see the code of it. (I have all the necessaary previleges.) Please tell me how I can I do this.

Thanks in advance.
Regards,
Ram.

View 7 Replies View Related

Search Stored Procedure

Apr 30, 2008

Hello

I have a text box on my web form where the user can enter multiple comma delimited search words. The value from this text box goes to my search stored procedure in the form of a search string.

I am able to parse this comma delimited search string and get the words into a temp table.

If there are 2 words in the temp table then this is the sql that I want

select * from Items
where (description like word1 or tagnumber like word1 or user like word1)
and (description like word2 or tagnumber like word2 or user like word2)

description,tagnumber, user or the fields of the Items table.There could be any number of words in the search string.

Any ideas of how to get this done with any number of search words being matched against number of column/s.

Any help regarding this is appreciated.

Thank you.

View 4 Replies View Related

Stored Procedure - Advanced Search

Oct 9, 2006

HIi want to create a procedure, basically i'll have 4 input parametres (Title, Category, ReleaseClass and BuyPrice)title will come from a textbox, the rest from dropdownlistsif a user enters a title, selects a ReleaseClass and BuyPrice, But doesnt select a category, all categories should be returned - You know what i meanhow do i go about this - Any Ideas??Cheers!!!

View 7 Replies View Related

Need To Build A Search Stored Procedure

Feb 21, 2007

I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.
I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?
 

View 5 Replies View Related

How To Search In 2 Tables By Using SQL Stored Procedure??

Mar 7, 2008

Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved