Stored Procedures IF Statement (SQL2005)

Jan 30, 2006

Hello, I need to use a if statement in stored procedures.

What I need to know is the proper syntax because anything I try does not work.

I need to check if 24 hours passed:
datediff(hh, Users.LastLogin, GetDate())

Should return the number of hours that has passed. I need to use this in the IF statement, and I need to check if it's > 24.

Thanks =)!

View 2 Replies


ADVERTISEMENT

SQL2005 Stored Procedures Don't Execute After Attach Database

Sep 19, 2006

While doing maintenance on a development server we detached a database and later reattached it to the same server.

For some unknown reason web services execute but return no results. There are no errors reported in event viewer. I connected to the database in Visual Studio 2005 and attempted to execute a simple select statement but it returns no row.

It appears to execute the stored procedure but does not return any data. I have cut and pasted the select statement into a View which does execute.

Lastly I just created a new store procedure to test the database and it also does not return any rows. This is all very strange.

Could use a few suggestions at this point.

Larry

View 2 Replies View Related

Stored Procedures IF Statement

Sep 12, 2007

I have a webpage with a grid view in it that has sorting and filtering options.  I am trying to take the sql statement and turn them into stored procedures.  The problem I am having is converting this into a stored procedure.
SQL = "Select CustSite.CustSite, FileInfo.FileID, FileInfo.Date, FileInfo.OriginalFileName, ReportType.ReportType, Equip.Equip, Tech.Name From [CustSite], [FileInfo], [ReportType], [Equip], [Tech] Where(FileInfo.CustomerID = '" & Session("CustomerID") & "') AND (CustSite.CustSiteID IN (Select Customer_CustSite.CustSiteID From [Customer_CustSite] Where(CustomerID = '" & Session("CustomerID") & "'))) AND (ReportType.ReportTypeID = FileInfo.ReportTypeID) AND (Tech.TechID = FileInfo.TechID) AND (Equip.EquipID = FileInfo.EquipID) AND (Equip.CustSiteID = CustSite.CustSiteID)"
If Site.SelectedItem.Value <> "" Then
SQL += " AND (CustSite.CustSiteID = '" & Site.SelectedItem.Value & "')"
End If
If Date_Set.SelectedItem.Value = "Single" Then
SQL += " AND (FileInfo.Date = '" & Start_Date.SelectedItem.Value & "')"
ElseIf Date_Set.SelectedItem.Value = "Between" Then
SQL += " AND (FileInfo.Date Between '" & Start_Date.SelectedItem.Value & "' AND '" & End_Date.SelectedItem.Value & "')"
End If
If Report_Name.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.FileID = '" & Report_Name.SelectedItem.Value & "')"
End If
If Report_Type.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.ReportTypeID = '" & Report_Type.SelectedItem.Value & "')"
End If
If Equipment.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.EquipID = '" & Equipment.SelectedItem.Value & "')"
End If
If Tech.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.TechID = '" & Tech.SelectedItem.Value & "')"
End If
 
 Can someone point me in the right direction?  Thanks in advance for the help.

View 1 Replies View Related

If Statement In Stored Procedures Help

Jun 23, 2008

Hi all. This has kept me up all night! I'm trying to populate a datgrid with multiple tables with relating keys in each table.

For ease, I've made the databases hopefully understandable.

DoctorsDB - (ID, Name, Phone)
FriendsDB - (ID, Name, Phone)
PetsDB - (ID, Name)
PeopleISawTodayDB - (MyID, MyName, DocID, FriendID, PetsID)

Now I need to generate a datagrid with the following details:
MyID, MyName, Doc's Name, Friend's Name, Pet's Name.

I have some success with the following code:

PROCEDURE dbo.PeopleISawTodayDB
@ID as Int
AS
BEGIN
SELECT
PeopleISawTodayDB.MyID, PeopleISawTodayDB.MyName, PeopleISawTodayDB.DocID, PeopleISawTodayDB.FriendID, PeopleISawTodayDB.PetID, DoctorsDB.ID, DoctorsDB.Name, FriendDB.ID, FriendDB.Name, PetDB.ID, PetDB.Name
FROM
PeopleISawTodayDB INNER JOIN
DoctorDB ON PeopleISawTodayDB.DocID = DoctorDB.ID INNER JOIN
FriendDB ON PeopleISawTodayDB.FriendID = FriendDB.ID INNER JOIN
PetDB ON PeopleISawTodayDB.PetID = Pet.ID
WHERE PeopleISawTodayDB.JobID=@ID
END



BUT it will only make a row appear if there are ID's in each of the PeopleISawTodayDB respective ID fields.

If I want to leave one blank (as in I didn't see that person that day), I would like it to still find the other details and populate the datagrid.

Can anyone lend a coding hand???? Cheers

View 7 Replies View Related

Calling Stored Procedures In SQL Statement

Aug 25, 2007

How can we call Stored Procedure inside any SQL Statement
For Example.
If we have procedure name sprocCurrentPriority
select * from tablename where colmunname = exec sprocCurrentPriority

View 7 Replies View Related

One Stored Procedures (SQL Statement) Question

May 9, 2005

Hi Everyone,
I have a Auto database table which store company auto business records.
If I want to know how many BMW still in store, I use Stored Procedure as
<code>Select Count(*) From AutoAd Where Make = 'BMW' and InStore = 1.
</code>
Now I want to know how many autos in store for all make (BMW, AUDI, HONDA, FORD...).  Does single stored procedures can instead twenty stored procedures to solve this question?  If can, please help me to show your stored procedures and how to reture the result to .NET program.
Thanks,
Lin

View 1 Replies View Related

USE Statement In Stored Procedures Two Databases, How Please?

May 23, 2008

This works great in a query:


USE KCADOWNLOAD

USE OUTHOUSE

GO

DELETE FROM OUTHOUSE.KCA_BLDGS_ACCES

INSERT INTO OUTHOUSE.OUTHOUSE.KCA_BLDGS_ACCES

(MAJOR, MINOR, ACCYTYPE, ACCYDESCR,

QUANTITY, SIZE, UNIT, GRADE, EFFYR, PCNTNETCONDITION, ACCYVALUE, DATEVALUED, UPDATEDBY,

UPDATEDATE,PID)

SELECT MAJOR, MINOR, ACCYTYPE, ACCYDESCR,

QUANTITY, SIZE, UNIT, GRADE, EFFYR, PCNTNETCONDITION, ACCYVALUE, DATEVALUED, UPDATEDBY,

UPDATEDATE,PID

FROM KCADOWNLOAD.dbo.KCA_BLDGS_ACCES AS KCA_BLDGS_ACCES_1



But the stored procedure only creates ONE Use statement:


USE [KCADOWNLOAD]

...etc.


I assume I can run an SP utilizing two databases...


The SP fails because it hasn't got a clue what OUTHOUSE is...


Thank you!!!

View 3 Replies View Related

... Executing Stored Procedures In An INSERT Statement ...

Aug 28, 2002

I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.

The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.

I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.

Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?

TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);

MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1

UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence

RETURN @sequence_id
END

View 1 Replies View Related

Calling Stored Procedures In A Select Statement

Feb 26, 2004

I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated

View 4 Replies View Related

Stored Procedures - Using Return Value From Dynamically Created SQL Statement

Jul 29, 2007

Hello,

This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.

I'm writing a stored procedure, in which I dynamically create an SQL statement.
At the end of this, the SQL statement reads like:



Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0



Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.

If I hard code a statement, like



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger
that works fine (although it brings back a count of all the lines).

But if I modify the dynamically created SQL Statement from earlier on to:



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
it doesnt work - it complains: Must declare the scalar variable "@LineCount".

Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)

So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?


View 3 Replies View Related

Transact SQL :: Find All Stored Procedures That Reference Oracle Table Name Within Server OPENQUERY Statement

Aug 10, 2015

One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?

View 2 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Runnaway UPDATE Statement In SQL2005 - How To Resolve?

Jun 16, 2006

I am in the process of moving a SQL2000 database to a SQL2005 database.

Porting from: SQL200, Windows Server 2000(SP4) (32 bit dual processor 4GB RAM)
to:SQL2005, Windows Server 2003(SP1) (x64 bit dual processor 4GB RAM)



After porting the database from SQL2000 to SQL2005 (no changes)
I then compare an update statement running from Management Studio on the 2003 Server and and Query analiser on the 2000

Server.

SQL2000 completes the command in 2 minutes SQL2005 is still running after 60 minutes.

SQL2000 is the live/production system with users connected, the SQL2005 is in a test environment with no other processors

running.

The SQL2005 activity monitor shows:
-----------------------------------
3 suspended processes in CXPACKET wait state and
2 runnable process high CPU counts (SQLServer running at 100% cpu).
All processes with the same Process ID.
Wait time is 0
High CPU counts for the runnable processes.
Low physical IO
No lock conflicts


When I add the "option (maxdop 1)" to the update statment then:

The activity monitor shows:
---------------------------
1 runnable process with a high CPU count (SQLServer running at 50% cpu).
Wait time is 0
High CPU count for the runnable processe.
Low physical IO
No lock conflicts

How do I debug this situation?
Why is the SQL2005 unable to complete the task?

The update statement is as follows...
-------------------------------------

update BI_LENDING_TRANSACTIONS
set [Balance Movement Month] = M.[Balance Movement Month]
from BI_LENDING_TRANSACTIONS as T,
BI_BALANCE_MOVEMENT_DATES as M,
BI_COMPANIES as C
where (T.[Transaction Date] >=
(SELECT DATEADD(d, - 70, minDate) from (select min([Transaction Date]) minDate
from p_BI_LENDING_TRANSACTIONS) t1)
OR
T.[Transaction Date] >= C.[MostRecentSnapShotDate] or
T.[Value Date] = T.[Balance Movement Month] ) and
T.[Value Date] <= C.[MostRecentSnapShotDate] and
T.[Value Date] >= T.[Transaction Date] and
T.[Company_Code] = M.[Company_Code] and
T.[Value Date] > M.[SnapShotFromDate] and
T.[Value Date] <= M.[SnapShotToDate] and
C.[Company_Code] = M.[Company_Code]

View 9 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Can I Use My Own Stored Procedure Template With SQL2005?

Oct 25, 2007

When I go to create a new stored procedure, function, etc. I get a default template... is there anyway to replace this template with one of my own? One that will already have the important information pre-populated (the stuff I always end up having to type in, like company name, etc.).

View 1 Replies View Related

Using System.IO In A SQL2005 CLR Stored Procedure

Mar 1, 2007

I am attempting to develop a stored proc that will do the following:

1)  Take as an input parameter the filepath of a local directory

2)  Return a recordset showing all files contained in the local directory

At the code level, I am attempting to do the following:

1)  use system.io class to iterate through each file of a given local directory

2)  parse out a union query to show all file names and their system creation times

3)  insert the parsed sql into a sqldatareader, and send that out via a sqlpipe to the caller. 

After compiling and deploying the CLR stored proc, I get the following when I try to execute:

Msg 6522, Level 16, State 1, Procedure spclr_wcl_get_file_info, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spclr_wcl_get_file_info':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path)

at StoredProcedures.spclr_wcl_get_file_info(String str_dir)

This occurs even though I've set the SQL Server service to run as a local administrator on the machine (ie, the account should have full rights to virtually any local directory or file). 

I found the following article which I thought might resolve:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494009&SiteID=1

but when I include the WindowsImpersonationContext, etc., I instead get the following:

Msg 10312, Level 16, State 49, Procedure spclr_wcl_get_file_info, Line 0

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

Was hoping someone could illuminate as to what I'm doing wrong?  Below is a code sample.  TIA.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Diagnostics;

 

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spclr_wcl_get_file_info(string str_dir)

{

// this clr stored proc will read all files in the trans log directory and return

// a recordset with file names and create dates.

//impersonate the calling user

System.Security.Principal.WindowsImpersonationContext newContext;

newContext = SqlContext.WindowsIdentity.Impersonate();

//enumerate files in directory, use to parse out union select query, return table with file names and create times.

string str_sql = "";

System.DateTime d_date;

FileInfo obj_fsi;

string[] str_arr_files = Directory.GetFiles(str_dir);

foreach(string str_file in str_arr_files)

{

obj_fsi = new FileInfo(str_dir + str_file);

if (obj_fsi.Exists)

{

d_date = obj_fsi.CreationTime;

str_sql += "SELECT CHAR(39)" + str_file + "CHAR(39) AS [file_name], ";

str_sql += d_date.ToLongDateString() + " file_creation_time UNION ";

}

}

// parse off the last 'union'

if (str_sql.Length > ("UNION ").Length)

str_sql = str_sql.Substring(0, str_sql.Length - ("UNION ").Length);

// use sql to send dataset back to caller.

SqlCommand obj_cmd = new SqlCommand();

obj_cmd.Connection = new SqlConnection("Context connection=true");

obj_cmd.Connection.Open();

obj_cmd.CommandText = str_sql;

SqlDataReader obj_reader = obj_cmd.ExecuteReader();

SqlPipe obj_pipe = SqlContext.Pipe;

obj_pipe.Send(obj_reader);

//clean up

obj_reader.Close();

obj_cmd.Connection.Close();

}



};

View 4 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

SQL2005 Passing GETDATE() As A Stored Proc Parameter

May 2, 2008

What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!

View 5 Replies View Related

Creating Extended Stored Procedure Using Vs.net 2005 For Sql2005

Jul 13, 2007



Hi,

web searches give no end of how extended stored procedures can only be written in C++ ( or maybe vb also) .



And that extended stored procedures should be abandonded in favour of CLR framework procedures.



And how most articles explain how to convert ESPs to CLR procedures!!!!!



But I need to pass a non-discript block of binary data, extract pieces of data identified by its offset into the block, data type inferred by offset; into data to be written to the SQL database. These offsets are determinede by mapping (C UNION) to C typedef structures.



This cannot be done by managed code, therefore cannot be done by C++ CLR.



It is also ill suited for C# .



Sounds like a job for C++ extended stored procedure.



But how does one create and deploy an ESP with Visual Studio 2005? All wizards seem to insist on CLR.



Help!?



Boyd

View 2 Replies View Related

Create Multiple Store Procedures In 1 SQL Statement

Nov 17, 2006

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

View 5 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

SQL2005 + Service Broker + CLR Stored Procedure + XML + Temp Tables

Feb 8, 2008

This is killing me...and not slowly.

I have a stored procedure (a) which calls another stored procedure (b).

Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements

If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.

But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.

Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable

- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.

The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.



----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.

If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.

View 3 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Sql2005 Database Restore From Another Sql2005 Backup File Error.

Dec 15, 2005

hi

i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :

 

 

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


 

 

 

pls some one can help me ???

 

thanks

chaus

View 62 Replies View Related

Can You Open A Database Created In SQL2005 In SQL2005 Express?

Oct 12, 2007



Can you open/use a database created in SQL2005 in SQL2005 Express?

Thanks for the help!

Max

View 4 Replies View Related







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