473,326 Members | 2,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Stored Procedure to change default

Can someone explain to me why the following doesn't work?

declare @oname sysname
select @oname=name from sysobjects where name like
"df__mytable__mycol%"

alter table mytable drop constraint @oname

Dec 14 '05 #1
4 1177
Maybe more than one row is returned, and only one value can fit into a
variable.

This will work:

declare @oname sysname
select TOP 1 @oname=name from sysobjects where name like
"df__mytable__mycol%"

alter table mytable drop constraint @oname

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<JL*******@Enlighten.Net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Can someone explain to me why the following doesn't work?

declare @oname sysname
select @oname=name from sysobjects where name like
"df__mytable__mycol%"

alter table mytable drop constraint @oname

Dec 14 '05 #2
Thanks, but that didn't do it. I get the same error:

Line 3: Incorrect syntax near '@oname'

Dec 14 '05 #3
(JL*******@Enlighten.Net) writes:
Can someone explain to me why the following doesn't work?

declare @oname sysname
select @oname=name from sysobjects where name like
"df__mytable__mycol%"

alter table mytable drop constraint @oname


Why should it work? There are some places you can use variables in
T-SQL for object names, but in most places you can't. Check out the
syntax chart in Books Online when in doubt.

You can use dynamic SQL: EXEC('ALTER TABLE tbl DROP CONSTRAINT ' + @oname)

For more information about dynamic SQL, see this article on my web site:
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 14 '05 #4
>Why should it work?<
What kind of question is that? Not relevant here. I asked why it
doesn't...

thank you for your response, not only did you solve my problem (using
"exec") but you educated me on the fact that I can't use variables for
object names in most places.

PS.
It should work because it's much more intuitive than having to create
and execute a string of the same command.
It should work because that would be consistant with other T-SQL
commands

Dec 15 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
4
by: Shawn Fletcher | last post by:
Hi, I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of No like we need it to. I have a...
13
by: dawatson833 | last post by:
I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure. If I...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
10
by: Eric E | last post by:
Hi all, I am using an Access client linked to a PG 7.4 server via ODBC. I have a stored proc on the server that inserts rows into a table.particular table, accomplished via an INSERT within the...
2
by: kanda | last post by:
Hello. I am developing the application (VBA&ODBC, to be exact) which periodically calls the stored procedures in the IBM DB2. A few of the procedures require executing with isolation level RR (...
4
by: nishi57 | last post by:
I hope I can get some help regarding this issue, which has been going on for a while. I have a desktop user who is having problem running "Stored Procedures". The DB2 Connect application works fine...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
7
by: jamesclose | last post by:
My problem is this (apologies if this is a little long ... hang in there): I can define a function in VB.NET with optional parameters that wraps a SQL procedure: Sub Test(Optional ByVal Arg1...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.