Simple Procedure Help
Dec 20, 2006
People I am not a SQL Server guy so bear with me please. This should be very simple. I am looking for a procedure; given a TABLE_NAME as input; will run the following three SQL statements for each COLUMN in that table:
select max(COLUMN_NAME) from TABLE_NAME
select count(0) from TABLE_NAME where COLUMN_NAME is null
select count (distinct COLUMN_NAME) from TABLE_NAME
It will then write the output in a readable fashion to a local file.
Thanks a lot
View 4 Replies
ADVERTISEMENT
Mar 3, 2008
Hello Everyone,I am sure, for the knowing, that this is a simple question.I have a table called tblTickets and a table called tblProjects. I would like to make a stored procedure that does the followingCopy entire contents of tblTickets record to tblProjects record and also changes tblTickets.sDetails to contain some text like "This ticket has been escalated into a project click here to view the project"tblProjects.sDetails would remain the same as the original.tblProjects will have additional fields but they can remain blank for the time being.ThanksMatt
View 11 Replies
View Related
Jun 3, 2008
What I am doing:I am in the process of creating a "log" that records when I update a record. I would like a new row to be inserted into BookLog each time a book's information is updated. Below I have the "update" code and below that is the code that is suppose to be inserting the newely updated record in the BookLog table.
**What the problem is**: I've tested the insert code, everything works EXCEPT BookEntryDate. I do not want to update the BookEntryDate when I am inserting it into BookLog. I just want to keep the original entry date from the Books table thats already there. In the BooksLog table I have "BookEntryDate" to not allow nulls...when i go to test i get an error that says "Procedure 'BookUpdate' expects parameter '@BookEntryDate', which was not supplied." I've tried several things and I am stuck :-/ I need some ideas. Thanks for your help!Alter Procedure dbo.BookUpdate@BookEntryDate datetime,@BookSummary nvarchar(1000),@BookComment nvarchar(50),@UserID nvarchar(50),@BookID bigint AsDeclare @Now datetimeSet @Now = GetDate()Update BooksSet Books.BookSummary = @BookSummary,Books.UserID = @UserID,Books.BookComment = @BookCommentWhere Books.BookID = @BookIDINSERT INTO BookLog(BookID,BookEntryDate,BookSummary, UserID,BookCommentLogDate)VALUES (@BookID,@BookEntryDate, @BookSummary,@UserID, @BookComment,@Now)RETURN GO
View 4 Replies
View Related
Jun 4, 2008
hi, i have a sqlserver2005 table 'member' with the fields 'id','name','datecreated','datemodified' and 'memberlevel' (which the default value is 1).
Now i want to create a stored procedure that will run automatically once a day,
this procedure will reset to 0 the 'memberlevel' that are 1 if the 'datemodified' is greater than 3 months.
can anyone help me on this, thanks!
View 2 Replies
View Related
Jun 12, 2008
When I verify syntax, I get this error:
Msg 1038, Level 15, State 4, Procedure webservices_BENEFICIAL_USES_DM_SELECT, Line 8
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Here's the proc:
CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME varchar(40) = ""
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT*
FROMBENEFICIAL_USES_DM
END
GO
Thanks.
View 6 Replies
View Related
Mar 31, 2007
Hey all,
Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation]
@Id int,
@Name varchar
AS
DELETE FROM Classifieds_Terminals
WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this:
USE [CLASSIFIEDSDB2.MDF]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RemoveLocation]
@Id = 18,
@Name = N'Terminal A'
SELECT 'Return Value' = @return_value
GO
It returns 0 and nothing happens....???
Here is the table:
id | Name
18 Terminal A18 Terminal B18 Terminal C
View 2 Replies
View Related
Apr 2, 2007
Hi,
I got a problem of a sp. Did the sp can use over one GO script? It is because after I create the below scripts in the sp and checked no syntax error and then apply changed. I re-open this sp, it just save my scripts upto the first GO statement . The scripts from "IF EXISTS..." has not saved. But I try to run the script in analyzer is successful. How can I fix it in Enterprise manager?
Thanks.
Here is my test code:
CREATE PROCEDURE sp_Test AS--set nocount onSELECT id, name from MainGO--insert the record in SimpleReports tableIF EXISTS (select ID from Main where id=5)delete from Main where id=5GOINSERT INTO Main ( ID, Name, Quantity)VALUES ( '6', 'hij', '60')GO
View 4 Replies
View Related
Jan 16, 2008
I have what I hope is a simple question about a stored procedure. If this is not the right spot for this, I offer my apologies.
I am planning on calling a stored procedure from my ASP.Net site using C#. What I want to do is pass a variable to the procedure and using that varible, put together another variable that I will pass back.
I am very new to all but the most basic stored procedures so I was not shocked when my code didnt work. Can anyone toss me a hint? I am actively working on this and if I find anything on a website to assist and have a breakthru, I will be sure to update you all.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goCREATE PROCEDURE [dbo].[GetAttySectyData] (@ID as int) AS
declare @First as varchar(200)
declare @Middle as varchar(50)
declare @Last as varchar(200)
declare @Name as varchar(255)@First = SELECT value FROM dbo.text WHERE efield_id = 10741 AND employee_id = @ID
@Middle = SELECT value FROM dbo.text WHERE efield_id = 10906 AND employee_id = @ID
@Last = SELECT value FROM dbo.text WHERE efield_id =10740 AND employee_id = @ID
Select @First+' '+@Middle+' '+@Last from text AS @Name
RETURN
View 3 Replies
View Related
May 21, 2008
I am using SQL Server Express within visual studio and am needing to create a stored procedure. The proble is that every time I finish and close it then it asks me if I want to save the changes which of course I want to.
But then it always comes up with a message box that says "invalid object name" followed with the name of the stored procedure.
??? why? I try creating a stored procedure from scratch or copying the code of another stored procedure. It alway gives me that message and does not let me save the stored procedure. I tried changing the default name of "dbo.storedprocedure1" or even leaving it as is and always I get the message that says "invalid object name: dbo.storedprocedure1" and it does not let me save the procedure.
What am I doing wrong?
View 5 Replies
View Related
Jan 28, 2005
I am trying to figure out the syntax for an SQL statement I have in a stored procedure.CREATE PROCEDURE dbo.forum_post_add
@forumId integer,
@fpTopic char(100),
@fpBody text,
@fpPoster integer
AS
Insert into forumPost (forumId, fpTopic, fpBody, fpPoster) values (@forumId, @fpTopic, @fpBody, @fpPoster)
UPDATE forum SET forumThreads = forumThreads + 1 where forumId = @forumId
UPDATE forumGroup SET fgThreads = fgThreads + 1 where fgID = select forumGroup from forum where forumId = @forumId
GOThe statement in question is the second UPDATE. How do I create an UPDATE statement that selects that correct record based on the single result of a SELECT statement?
I am also not too sure about the 'fgThreads = fgThreads + 1' thing, is this the best way to increment a value?
I know I should also be using transactions and probably a few other bells and whistles but I'll worry about that later.
Thanks
Martin
View 4 Replies
View Related
Jul 14, 2005
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
what are mine doing wrong ?
Please help
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = password
FROM CandidatesAccount
WHERE username = @username and Activate = 1
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO
View 5 Replies
View Related
Feb 13, 2007
If I put 5 or 6 stored procedures within another stored procedure will they be executed sequentially or all at once? Is there any method. I can manage this process with some code I was just wondering how query analyzer or an sp would handle this by default.
create dbo.storedprocedure as update_data
exec sp_1
exec sp_2
exec sp_3
etc.
View 12 Replies
View Related
Aug 16, 2006
Dear experts,Again, sorry to bother you again with such a seemingly dumb question,but I'm having some really mysterious results here.ie.Create procedure the_testAsBeginSelect CustomerID
Quote:
View 14 Replies
View Related
Dec 4, 2006
Hi All,I am pretty new to stored procedure and I have a quick question. Iappreciate any help offered.I am writing a stored procedure as follow. This is just an example:select field1, field2 from table1 where field1 = 1I need to change this stored procedure so that if field2=0 then it willbe displayed on the web application as 'Yes', if field2=1 then I needto display the text 'No' on the web application.Field2's type is bit, ie: its' value is either 1 or 0.Thanks for any suggestion.Sincerely,Teresa
View 2 Replies
View Related
May 29, 2008
Hi I have a table that has several related keys into other tables with the main table looking like
System ID buildingID roomID ColorID
1 prikey 3 4 5
2 2 1 3
building table looks like
buildingID building name
1 prikey Flower bld
2 Lab bld
3 Crafts bld
4 Eng bld
room table looks like
roomID room name
1 prikey 100
2 101
3 102
Color table looks like
colorID Color name
1prikey red
2 blue
3 grey
4 white
I will need to pull the data from the system table based building name, room name and color. If the parameter is zero I will not filter on that parameter so will only be filtering on one parameter for each time the procedure is called. Any ideas on a simple query, thanks!
View 4 Replies
View Related
Mar 24, 2008
I'm executing simple stored procedure same to:
create procedure [dbo].[usp_procedure]@var1 int=null output,@var2 varchar(25)=null output,@var3 varchar(25)=null output,@var4 varchar(25)=null output,@var5 varchar(25)=null output,@action char(1)=null outputasbegin set nocoutn on;if @action = 'S'begin select * from t_table order by @var1end
in my script I call to this stored procedure but, this send me a error. my script is
$action="S";// Set up T-SQL query.$tsql = "call dbo.usp_T_Usuarios (@action=?)";// Assign parameter values.$params = array($action);// Specify types for parameters.$stmt = sqlsrv_query( $conn, $tsql, $params);echo "<br><br>Executed.....<br><br>";// Create and execute the statement. Display any errors that occur.if(!$stmt){ echo "Error executing the sp...<br>"; die( print_r( sqlsrv_errors(), true));}else{ echo "<br>Execute sp....<br><br>"; echo $stmt;}
and the error is:
conected...The conection was succefully.Executed.....Error executing the sp...Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido [message] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido [message] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido ) )I need help me. some body tell me what is my error. please.
View 9 Replies
View Related
Jan 28, 2008
Hi all,
I need to get a better understanding of alter procedure. If I Create a Procedure in SQL Server 2005, and want to modify that to Alter Procedure statement instead of create procedure... How do I do that??
I'm not an SQL Server person.. I dont understand why my work place needs Alter proceudre all the time but all our procedures have Alter procedure statement. Once they are created they have to be changed to Alter procedure...
Do I just need to change the keyword CREATE to ALTER?? because when I try to save it ,,, it saves it as a .sql file... Not the script.
Immediate reply would be appreciated
Thanks
View 7 Replies
View Related
May 19, 2007
I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.
View 7 Replies
View Related
Apr 9, 2008
I suspect I have a but in a stored procedure that is used to delete records in a table. Here's the code for the gridview and its SQLDataSource
1 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
2 DataKeyNames="table2_id" DataSourceID="SqlDataSource1">
3 <Columns>
4 <asp:CommandField ShowDeleteButton="True" ShowSelectButton="True" />
5 <asp:BoundField DataField="table2_id" HeaderText="table2_id"
6 InsertVisible="False" ReadOnly="True" SortExpression="table2_id" />
7 <asp:BoundField DataField="table1_id" HeaderText="table1_id"
8 SortExpression="table1_id" />
9 <asp:BoundField DataField="name_2" HeaderText="name_2"
10 SortExpression="name_2" />
11 <asp:CheckBoxField DataField="yesno" HeaderText="yesno"
12 SortExpression="yesno" />
13 <asp:BoundField DataField="MyArtificialColumn" HeaderText="MyArtificialColumn"
14 ReadOnly="True" SortExpression="MyArtificialColumn" />
15 </Columns>
16 </asp:GridView>
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server"
18 ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
19 DeleteCommand="EraseIt" DeleteCommandType="StoredProcedure"
20 SelectCommand="StoredProcedure5" SelectCommandType="StoredProcedure">
21 </asp:SqlDataSource>
Recently, "Wreck of u" kindly provided me with the stored procedure "StoredProcedure5" (line 20) which populates the gridview as shown. What I would like to now is write a stored procedure that DELETES a row. As like 19 shows, I attempted to write such an SP called EraseIt. However, when I click on Delete in the browser for a row, I get the error message "Procedure EraseIt has no parameters and arguments were supplied. The following is the code for EraseIt. Do I in fact have an error with specifying the input parameter "@table2_id", and or am I doing something else wrong? 1 ALTER PROCEDURE [dbo].[EraseIt]
2 AS
3 DECLARE @TABLE2_ID int
4 BEGIN
5 DELETE FROM DBO.TABLE2 WHERE TABLE2_ID = @TABLE2_ID
6 END
7
Thanks!-Kurt
View 5 Replies
View Related
Oct 29, 2014
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
<table>
<tr>
<td>cell1</td>
<td>cell2</td>
<td>cell3</td>
[Code] .....
This is my attempt but I can't figure out how to get separate cols
declare @GridData xml = '<table><tr><td>cell1</td><td>cell2</td><td>cell3</td></tr><tr><td>cell4</td><td>cell5</td><td>cell6</td></tr><tr><td>cell7</td><td>cell8</td><td>cell8</td></tr></table>'
select T.C.value('.', 'nvarchar(max)')
from @GridData.nodes('//tr') T(C)
View 6 Replies
View Related
Oct 16, 2007
Hi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
View 1 Replies
View Related
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
Mar 24, 2007
...when I started this endeavor.
I have a previously developed Lotus Notes App. The idea was simple; as
I am sefl taught on Lotus Script, I figured I'd be able to stumble my
way through VB.Well,
it started OK. I used VB Express to get familiar with the stuff, but
decided to go with a full version of VS 2005 and try and get this thing
properly developed as a web app. I purchased several reference books
etc., and have become relatively familiar with the forums here.First
issue I have is that I simply want to use code to update or add records
to an SQL DB. I know about datagridview etc., but I want to update the
DB using forms, not the tabular view those controls provide. I thought
it would be relatively straight forward, but found my ignorance runs
deeper than I thought. When I tried to do so I am finding I am not
really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The
issue with searching these forums is most posts deal with datagridview
or something similar.I have spent a ton of time trying to find relevant
posts or articles, but have had no luck yet.Again, all Ireally
need is a nudge in the right direction. I am more than willing to plod
through reference materials or articles/posts to find what I need to
know, I just can't find where to even start on the info I need. Regards, Joe
View 3 Replies
View Related
Feb 24, 2008
Hi
I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work.
ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
View 2 Replies
View Related
Dec 30, 2004
How can i get an output like this from this sql??
HEM_PATIENT_ID is primary key...
Output:
First row: initial values of the fields
Second row: average of the same fields
Please help me...
select * from (
select HEM_LOKOSIT, HEM_NNS
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33
union
select AVG(HEM_LOKOSIT), AVG(HEM_NNS)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33)
order by HEM_LOKOSIT desc nulls last;
View 1 Replies
View Related
May 20, 2001
Am new to SQL Server , can any1 tell me is there anyway to display date in this format month and year like Apr 2000 ,excluding the day.
eg: 01/28/2000 should b displayed like Jan 2000
View 5 Replies
View Related
Jan 25, 2001
Hi,
I'm relatively new to SQL7 but I did use 6.5 a fair bit.
I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table
2)Add some data to the test table
3)Back up the transaction log
4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Thankyou,
Tim
View 4 Replies
View Related
Nov 8, 1999
Given one table with one column and two rows, one containing the string 'Bill', one containing the string 'Gates'.
Write a select statement which gives you the result 'Bill Gates'.
???+
View 1 Replies
View Related
Apr 2, 2003
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
View 1 Replies
View Related
Apr 2, 2003
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
View 1 Replies
View Related
