New answers tagged

0

The "granular updates" question is orthogonal to the question of whether to put it in a stored procedure, as you can break it down both in client code or in a stored procedure. I must say, I only ever see a point to breaking down update queries like this when dealing with very wide and/or heavily indexed tables, or where triggers are involved. The ...


3

Personally I don't understand why people write stored procedures that only perform CRUD. For me SQL is the interface to the database. Just write an update statement with placeholders for the column values and send that to the database -- along with the bound variables. Someone will argue about SQL injection, but a) you're just moving the point where that can ...


4

Stored procs are the programming API of the database, the methods you invoke to mutate the system state. As such, I prefer to have SPs names reflect the business outcome desired rather than the implementation - change_of_address rather than update_customer. Each SP has the parameters to perform that business change and no others. Each does one thing and does ...


0

I would definitely avoid the complication of packing and unpacking XML, effectively turning a single input parameter into a DSL. It is something extra that can have bugs at either end (and a bug is in the calling code, you may end up having to prove it isn't in your DB-side code before that is accepted!). Would extra parameters be acceptable for columns that ...


0

On your BCP command use the -t switch to specify which field delimiter you want to use https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15


0

Probably the easiest way will be to use the dbatools powershell library and a few lines of powershell. e.g. $filename = "C:\Users\way0u\OneDrive\Documents\SQL\Customer" Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance ".\SQL2019" | Export-Csv -Path "$filename.csv" -NoTypeInformation ...


0

I'd like to emphasize point 3 in J.D's post. This is something you definitely want to do outside of SQL Server, if at all humanly possible. That "outside of SQL Server" could be things like SSIS, Powershell, something you write in your favorite programming language (C#, python), some client app, etc.


0

A SQL Server instance has no concept of another machine other than it's own. When you connect to it with SSMS (or other means) you're specifically initiating the connection to that server, not the other way around. So out of the box I don't believe there is a way with pure SQL to accomplish this. These are a couple ideas of what you can do: xp_cmdshell ...


0

I would argue that transaction control does not belong to stored procedures at all, apart from some very particular circumstances (e.g. where you want to log audit records regardless of what happens to the "outer" transaction -- i.e. "autonomous transaction"). The procedure has no way of knowing if it is part of a larger unit of work and ...


Top 50 recent answers are included